EOMONTH function in Excel (How to + 8 examples)

One of Excel’s most useful functions for accountants, financial modellers and data analysts is the EOMONTH function. This function calculates the last day of a month, based on a specified number of months in the past or future.

From the name, EOMONTH, it may not be clear what the function does. Once we realize EOMONTH stands for “End Of MONTH”, suddenly its purpose becomes much clearer.

This function has a few quirks, so in this post, we look at the EOMONTH function in Excel and work through 8 examples.

Note: All dates in this post are shown in a dd-mmm-yyyy format (e.g., 15-Nov-2024).

Table of Contents

• Syntax and arguments
• Basic usage
• Error messages
• Examples
• End of the current month
• Start of a month
• Dates as Text
• Enter dates using the DATE function
• Month number for fiscal year
• Sum all values in a calendar month
• Spill EOMONTH with SEQUENCE
• Spill EOMONTH over an array
• Benefits of the EOMONTH function
• Conclusion

Watch the video

Watch the video on YouTube

Syntax and arguments

The EOMONTH function has 2 arguments:

=EOMONTH(start_date, months)

• start_date: a serial date number representing the start date. It can also be a text value provided Excel recognizes the format as a date (more on that later)
• months: the number of months before (negative number) or after (positive number) the start date.

Result: The function returns the serial number of the last day of the month, based on a specified number of months in the past or future.

If the value returned does not display as a date, but as a number, use number formatting to display the value as a date.

Basic usage

The screenshot below shows a simple example of using the EOMONTH function.

The formula in cell C5 is:

=EOMONTH(C2,C3)

This formula returns 29-Feb-2024. This is the end of the month, 3 months in the future from 15-Nov-2023

The months argument above contains a positive number. It can also include negative numbers to show month ends from the past.

This formula returns 30-Sep-2023. This is the end of the month, 2 months in the past from 15-Nov-2023.

Providing zero as the month argument returns the month-end date for the month of the start_date.

This formula returns 30-Nov-2023. The end of the month 0 months in the future.

Error messages

The following are possible error messages if EOMONTH is used incorrectly.

#VALUE

• Start_date argument is not a valid date format.
• A multi-cell range has been provided as the start_date (needs to be coerced to an array – more on this later).
• Months argument is not a valid number.

#NUM

• Start_date is not a valid date (e.g., before 01-Jan-1900)

• The result of the function is not a valid date (e.g., before 01-Jan-1900)

Examples

Let’s look at some examples to really understand the EOMONTH function in Excel.

End of the current month

To calculate the end of the current month we can use TODAY as the start_date argument.

=EOMONTH(TODAY(),0)

Result: Returns the month-end date based on today’s date.

Start of a month

A common method for calculating the start of a month is to use the DATE, YEAR, and MONTH functions.

The formula in cell C4 is:

=DATE(YEAR(C2),MONTH(C2),1)

Result: 01-Nov-2023

Instead, we could use EOMONTH to calculate the month-end date for the previous month (-1), then add 1 day.

The formula in cell C5 is:

=EOMONTH(C2,-1)+1

Result: 01-Nov-2023

Dates as Text

We can provide text values for the start_date argument. If Excel can convert the text to a date, the function will perform correctly.

Example:

=EOMONTH("2023-11-15",3)

Result: 29-Feb-2024

In the example above, start_date is in a yyyy-mm-dd format. Excel can understand this format.

Care must be taken with this method, as formats and region settings may understand dates differently in different regions.

Example

=EOMONTH("02-01-2024",0)

Result:

• In the US, the date above would be recognized as 1-Feb-2024, returning 29-Feb-2024 as the result.
• In the UK, the date would be recognized as 1-Jan-2024, returning 31-Jan-2024 as the result.

Enter dates using the DATE function

If we want to create dates manually, rather than using a text value, the DATE function is the best option.

DATE has 3 arguments: Year, Month, and Day

Example

=EOMONTH(DATE(2023,11,15),3)

• DATE(2023,11,15): calculates as 15-Nov-2023.

Result: 29-Feb-2024

Month number for fiscal year

If the financial year is not in sync with the calendar year, EOMONTH combined with MONTH is a great option for calculating the period number.

Example

Assuming the financial year ends on 31-Aug-2023, 15-Nov-2023 would be in the 3rd Month.

=MONTH(EOMONTH(DATE(2023,11,15),4))

• DATE(2023,11,15): Calculates as 15-Nov-2023
• EOMONTH(15-Nov-2023,4): As the year-end is August, the 8th calendar month, we need to uplift any month by 4 to get the correct result. For our example, 15-Nov-2023 is adjusted by 4 months, which returns 31-Mar-2024
• MONTH(31-Mar-2024): Calculates the month number.

Result: 3

Don’t worry, we are not using the March date; it is simply to offset the date so we can calculate the month number for our financial year.

Sum all values in a calendar month

Using EOMONTH within SUMIFS we can calculate the values for a calendar month based on a single date.

The formula in cell F5 is:

=SUMIFS(C3:C18,B3:B18,">"&EOMONTH(F3,-1),B3:B18,"<="&EOMONTH(F3,0))

This formula calculates Values from C3:C18 where the dates from B3:B18 are:

• Greater than last month’s end date (e.g., greater than 31-Jul-2023)
• Less than or equal to the month-end date (e.g., less than or equal to 31-Aug-2023)

Therefore, this formula sums all the values for Aug-2023 based on a single date.

Spill EOMONTH with SEQUENCE

The SEQUENCE function creates a list of numbers. We can use this list as the month argument in the EOMONTH function.

The formula in cell C6 is:

=EOMONTH(C2,SEQUENCE(1,C4,C3))

SEQUENCE creates a list of numbers with:

• 1 row (first argument)
• 6 columns (second argument, cell C4)
• starting at 0 (third argument, cell C3)

The result of SEQUENCE is {0;1;2;3;4;5}

This list is passed into the EOMONTH function to calculate a date for each item in the array.

Only cell C6 contains a formula, but the results spill over into D6:H6.

Spill EOMONTH over an array

EOMONTH can calculate results for each item in an array.

The formula in cell C3 is:

=EOMONTH(--B3:B18,0)

Unfortunately, using EOMONTH(B3:B18,0) by itself returns the #VALUE error. We must first convert the range to an array. We can achieve this by:

• Adding the double unary (double minus): =EOMONTH(—B3:B18,0)
• Multiplying by 1: =EOMONTH(B3:B18*1,0)
• Including + before the range: =EOMONTH(+B3:B18,0)

The above uses the double unary method.

Benefits of the EOMONTH function

The EOMONTH function provides us with several benefits.

Flexible time calculations: EOMONTH simplifies time-based calculations, offering flexibility in managing deadlines, budget cycles, and more.

Error Reduction: Date calculations are prone to errors, especially when accounting for varying month lengths and leap years. EOMONTH automates this calculation, reducing the chances of mistakes.

Conclusion

In this post, we’ve seen how to use the EOMONTH function in Excel in many scenarios.

Hopefully, you will agree it is more than just a date function; it’s a gateway to efficient and error-free date calculations.

Related Posts

• Power Query date format (How to + 5 tricky scenarios)
• How to subtract dates in Excel to get days
• Calculate quarter from dates in Excel (inc non-standard calendars)

---

What next?

Discover how you can automate your work with our Excel courses and tools.