Using MONTH and EOMONTH functions in Excel

In this easy-to-follow guide, we’ll explore the Excel MONTH and EOMONTH functions. These functions are super handy for working with dates. We’ll show you how to use them to figure out which month a date belongs to, get the first and last day of a month, and more. Whether you’re new to Excel or an experienced user, this tutorial will help you become a pro at handling dates in your spreadsheets. By the end, you’ll be able to breeze through date-related tasks and make your Excel work even smoother. So, let’s dive in and master these functions together!

MONTH and EOMONTH are built-in functions in Excel that assist users in performing date-based operations, particularly those tied to months. Let’s delve into each function and see how they can be useful:

Excel MONTH Function – Syntax and Uses

The MONTH function is primarily used to extract the month from the date entered. It gives output in numbers ranging from 1 to 12.

Syntax for MONTH function

MONTH (serial_number)

Applications:

• Sales Analysis – Extracting month from sales date and summarizing sales data
• Academics and Research – For researchers collecting data over time to categorize their findings month-wise. Institutions can also plan academic schedules, holidays, and examinations months.
• Healthcare – Analyzing patient visitation trends.

How to Get Month Number from Date in Excel

The input to the MONTH function can be:

• A serial number from which a valid date can be extracted
• Reference to cells containing dates
• Date values returned from Excel formulas

Formula:

= MONTH (DATE(2022,6,7)) returns 6 
As the date is 7th June 2022

= MONTH (D3) returns the month of date present in the cell D3

= MONTH (“07-June-2022”) also returns 6

Example:

Variations of inputs that can be passed to MONTH

How to Extract Month Name from Date in Excel

This can be achieved by using the TEXT function. But it is only applicable to a specific type of data code – DD-MMM-YY where the month is written using letters.

Formula:

= TEXT(B2, “mmm”) – it will return an abbreviated name of the month (upto three letters), like Jun for June

= TEXT(B2, “mmmm”) – it will return a full month name i.e. November, December

Example:

Extracting Month Name from Date using TEXT Function

How to Convert Month Name to Month Number in Excel

This can be achieved by a combination of two functions – DATEVALUE and MONTH.

DATEVALUE converts a date stored as text to a serial number. While MONTH extracts the month number from the date.

Formula:

= MONTH (DATEVALUE(D3 & “1”))

• D3 is a cell containing the month name we want to convert
• &”1″ is added to aid the DATEVALUE function to understand it’s a date

Example:

Converting month name to month number using MONTH and DATEVALUE functions

How to Convert Month Name to Number in Excel

Numbers 1-12 can be converted to their corresponding month names using the TEXT function. We need to multiply the number by 28 to convert it into a corresponding serial number understandable by the computer.

Formula:

= TEXT(B12 * 28, “mmm”) will return the abbreviated month name (upto three letters) i.e. Apr for April

= TEXT(B12 * 28, “mmmm”) will return the full month name i.e. November, December etc. 

The month name can be extracted from a date format as well. For example:

= TEXT(DATE(2016, B12, 7), “mmm”)

Here year and date are given whereas, B12 contains the month number. DATE will convert this information to a valid date code, from which TEXT will extract the month name.

Example:

Converting Month Number to Month Name using TEXT function

How to Get the Last Day of a Month – EOMONTH Function

EOMONTH function denotes the End of the Month and is used to calculate the end date given a specified time interval and start date.

Syntax for EOMONTH Function:

EOMONTH (start_date, months)

Months denotes the number of months before or after the start date. Positive values are used for finding dates in the future while negative ones for the past.

Example

=EOMONTH(D3, 4) – returns the last day of month 4 months after the date in cell D3

=EOMONTH(D3, -4) – returns the last day of month 4 months before the date in cell D3

=EOMONTH(“07-December-2023”, 0) or =EOMONTH(DATE(2023,12,7), 0) – returns last date of current month

=EOMONTH(TODAY(), 0) also returns last day of the current month

Example Operations using EOMONTH Function

Note – EOMONTH returns a serial number representing the resultant date. It can be converted into a comprehensive date

using the TEXT function. The TEXT function takes the value to be converted and date format type as its parameters.

Potential Errors that Can Occur

• #NUM! error can occur if the start_date is not a valid Excel date
• #VALUE error can occur if any of the supplied arguments are non-numeric.
• If a decimal value is provided for a month – the EOMONTH function will only take into account the integer part.

Applications

• Financial Modelling – This function finds its use in financial modeling, the process of finding a specific date can be efficiently automated with its help. It can be used in the monthly, quarterly, and annual financial models.
• Financial Analysts – It can also be useful for financial analysts when they have to calculate maturity dates for accounts payable or accounts receivable that fall on the last day of the month.

How to Get the First Day of a Month

There are many ways to get the first day of a month. Though there are various methods to find it, we can also make use of the EOMONTH function to get the first day. The various techniques are:

Get the 1st day of the month by the Month Number

We can make use of the DATE function to find out the first day of the given month number.

Formula:

= DATE(year, month number, 1)

We have to feed the current year, month number, etc. on our own in this case.

Example:

Finding first date of the month in which the given date lies

Get the 1st day of the Month from a Date

If we want to calculate the first date in relation to the month provided in a specific date then we can employ both DATE and MONTH functions to do so.

Formula:

=DATE(year, MONTH(cell with the date), 1)

The formula will return the first day of the month based on the date fed to the MONTH function, be it referenced from a cell or a direct date code.

Example:

Finding first day of the month in which the dates in the mentioned cells lie

Find the First Day of the Month Based on the Current Date

When we are required to work with the current date and cannot specify the required month or year, then we can make use of the EOMONTH and TODAY functions.

Formula:

= EOMONTH(TODAY(), 0) + 1 extracts the first day of the next month

= EOMONTH(TODAY(), -2) + 1 extracts the first day of the previous month

= EOMONTH(TODAY(), -1) + 1 extracts the first day of the current month

Example:

Finding the first date of the current month using the EOMONTH function

The same task can also be performed using MONTH, DATE, TODAY, and YEAR functions. One can use the YEAR function to extract the year and MONTH to extract the month from the current date and feed it to the DATE function, with 1 set as the day.

In order to obtain the first dates of previous or next months one can just subtract or add 1 to the month number returned by the MONTH function.

Formula:

= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) returns the first date of the current month

= DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) returns the first date of the following month

= DATE(YEAR(TODAY()), MONTH(TODAY()) – 1, 1) returns the first date of the previous month

Example:

Finding the first date of the current month using DATE, YEAR, and MONTH functions

Calculating the Number of Days in a Month

Excel doesn’t specifically provide a function to calculate the number of days in a given month. But there do exist a variety of functions that work with dates and times that we can employ for this purpose. 

Get the Number of Days Based on the Month Number

If the month number and year are known, then we can use a combination of DAY and DATE formulae to get the number of days in that month.

Formula:

= DAY(DATE(year, month_number + 1, 1) -1)

How this formula works is, that the DATE function will return the first day of the following month, from which when 1 is subtracted, we get the last date of the month wanted. The DAY function will convert the date to a number. 

Example:

Finding the number of Days in the required month by finding last date of the month

To Get the Number of Days in a Month Based on Date

If the month number is not known, but we do know any date within that month, then the YEAR and MONTH functions can be used to extract the year and month number from the date. Then we just need to input these into the DAY/DATE formula and the no. of days will be calculated.

Formula:

= DAY(DATE(YEAR(A13), MONTH(A13) + 1, 1) -1)

Alternatively, the EOMONTH formula can be used to just return the last day of the month, which is further converted to the number of days by extracting the day.

Formula:

= DAY(EOMONTH(A1, 0))

Example:

Finding the Number of Days using YEAR, MONTH, and EOMONTH functions

How to Sum Data By Month in Excel

We may want to get the sum of values for a particular month from tables that contain a large amount of data. Further problems may arise if the data is not in chronological order.

To solve this problem we can add a helper column that will convert dates to month numbers using a simple Excel MONTH formula.

The next step will be to list down numbers that are of interest in an empty column, and the sum values of each month using the following formula:

= SUMIF(D4:D14, F4, C4:C14)

Where F4 contains the desired month number.

Example:

Calculation of total sales per month using Helper Column

A shorter and simpler method to perform the same task without the helper column will be to use the formula:

= SUMPRODUCT((MONTH(C4:C14) = F4) * (D4:D14))

Here column D contains dates, column D contains the values to sum and F4 contains the month number.

How to Conditionally Format Dates Based on Month

To take the calculations and visual presentations a step further, we can use the capabilities of Excel conditional formatting for dates using the Excel MONTH and EOMONTH functions.

Highlight Dates Within the Current Month

If we want to highlight all rows with the current month dates we can follow the following steps:

• Extract the month numbers from dates in a column using the simplest =MONTH(A15) formula.
• Compare these numbers with the current month given by =MONTH(TODAY())
• Based on what the formula returns (TRUE or FALSE), create an Excel conditional formatting rule

Formula:

= MONTH(A15) = MONTH(TODAY())

Example:

Step 1: Select the Data and Create a New Rule with the formula and formatting in Conditional Formatting Manager

Select the Data and Create a New Rule with the formula and formatting in Conditional Formatting Manager

Step 2: Apply the Formula to the selected range

Apply the formula to the selected range

Highlighting Dates by Month and Day

If we want to highlight the gazetted holidays in our worksheet regardless of the year, for e.g. Diwali or New Year, then we can use the DAY and MONTH functions for the same.

Formula:

= AND (OR (DAY(A15) = 25, DAY(A15) = 31), MONTH (A15) = 12)

We use the DAY function to extract the day of the month (1-31) and the MONTH function to get the month number, and then check if the DAY is equal to either 25 or 31 and the MONTH is equal to 12.

Example:

Step 1: Create a New Rule in the Conditional Formatting Manager

Create a New Rule in Conditional Formatting Manager

Step 2: Specify the Formatting that needs to be applied to Cells for which the formula returns TRUE

Specify the Formatting that needs to be applied to Cells for which the formula returns TRUE

Step 3: Select the range for which the formula needs to be applied and click Apply and OK

Select the range for which the formula needs to be applied and click Apply and OK

Example Use Case: Budget Planning for a Company

A company prepares its budgeting plans on a quarterly basis. However, within each quarter, the company also has to set aside specific funds at the end of each month for certain mandatory expenses. Here’s how the EOMONTH function can be utilized in this context:

1. Determining Employee Bonus Allocation

Employees are given bonuses at the end of every quarter. To prepare for this, the financial analyst wants to know the exact end date of the month, 3 months from the current date, which denotes the end of the quarter.

If today’s date, for example, is “15-January-2024”, the formula in Excel would be:

=EOMONTH (TODAY(), 3)

Output:

45412 which represents 30 April 2024

2. Evaluating Quarterly Rent Payments

The company pays rent for its office spaces at the end of every quarter. The accounts department needs to know the last date of the month, 3 months prior, to ensure they’ve accounted for the past quarter’s rent.

Assuming today’s date is “15-April-2024”, the formula would be:

=EOMONTH (TODAY(), -3)

Output:

45230 which represents 30 October 2023

3. Year End Closing

The company closes its financial books annually. To know the exact end date of the current financial year, the analyst can use the formula:

If the financial year starts from “1 April 2023”, the formula would be:

=EOMONTH(DATE(2023, 4, 1), 12)

Output:

45412 which represents 30 April 2024

Frequently Asked Questions

Can I use the MONTH function to determine the quarter of the year?

The MONTH function can be used with other formulas to determine the quarter. For example, using the formula =INT( (MONTH(A1)-1) / 3 ) + 1 will return the quarter for the date in cell A1.

Can I use the EOMONTH function to get the start date of a month?

While the EOMONTH function primarily returns the end date of a month, it can be used in combination with other functions to get the start date. For example, if you want the start date of the next month, you can use =EOMONTH(TODAY(), 0) + 1. This formula first finds the end date of the current month and then adds one day to get the start date of the next month.

What is the difference between EDATE and EOMONTH?

EDATE: Returns the date that is the indicated number of months before or after the start date.

EOMONTH: Returns the date in date-time format of the last day of the month before or after a specified number of months.

What is the return value of EOMONTH?

The return value of the EOMONTH is a serial number that can further be converted into a readable date format using the DATE or TEXT function.