Excel EOMONTH Function
The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to move forward in time, and a negative number to move back in time.
- start_date - A date that represents the start date in a valid Excel serial number format.
- months - The number of months before or after start_date.
The EOMONTH function returns the last day of the month a given number of months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. The EOMONTH function takes two arguments: start_date and months. Start_date must be a valid Excel date. The months argument specifies how many months in the future or past to move – use a positive number to move forward in time, and a negative number to move back in time.
Example #1 - Basic usage
With May 12, 2017 in cell B5:
You can use EOMONTH to move through years as well:
Example #2 - last day of current month
To get the last day of the current month, combine the TODAY function with EOMONTH like this:
The TODAY function returns the current date to the EOMONTH function. EOMONTH, with zero (0) for months, uses the current date to calculate the last day of the current month.
Example #3 - First day of current month
Although EOMONTH returns the last day of the month, you can use EOMONTH to get the first day month of the current month like this:
See links below for more examples of how to use the EOMONTH function in formulas.
- For months, use a positive number for future dates and a negative number for past dates.
- EOMONTH will return the #VALUE error if the start date is not a valid date.
- If the start date has a fractional time attached, it will be removed.
- If the months argument contains a decimal value, it will be removed.
- To move any date n months into the future or past, see the EDATE function.
- EOMONTH returns a date serial number, which must be formatted as a date.