Excel EOMONTH function

Summary

The Excel EOMONTH function calculates the last day of the month a certain number of months away from a given date. This function is essential for operations where determining the end of a month is crucial, such as in financial planning, billing cycles, or setting monthly deadlines. EOMONTH can be used to project dates into the future (with positive month values) or into the past (with negative month values).
Syntax
				
					=EOMONTH(start_date, months)
				
			
  • start_date: The initial date from which the calculation starts, in valid Excel date format.
  • months: The number of months to add (positive value) or subtract (negative value) from the start_date.
Return value
A date representing the last day of the month after adding or subtracting the specified number of months.

How to use

The EOMONTH function is straightforward to use. Just input the start date and the number of months you want to add or subtract. The function will return the date of the last day of the resulting month. It’s particularly helpful for managing dates that must align with the end of a month.

Examples

Simple EOMONTH

Basic End-of-Month Calculation: To find the last day of the month, four months from a specific date:

				
					=EOMONTH("2020-01-15", 4)
				
			
This formula calculates the last day of the month, four months after January 15, 2020.
EOMONTH for Current Month End
Last Day of Current Month: To determine the last day of the current month:
				
					=EOMONTH(TODAY(), 0)
				
			
This formula calculates the last day of the month, four months after January 15, 2020.
EOMONTH for Current Month End

Last Day of Current Month: To determine the last day of the current month:

				
					=EOMONTH(TODAY(), 0)
				
			
Combining EOMONTH with the TODAY function, this formula finds the last day of the current month.
EOMONTH for First Day of Current Month

First Day of Current Month: To calculate the first day of the current month:

				
					=EOMONTH(TODAY(), -1) + 1
				
			
This formula uses EOMONTH to get the last day of the previous month and then adds 1 to find the first day of the current month.

Additional Notes

  • EOMONTH is a highly useful function in scenarios requiring precise month-end date calculations.
  • The function requires a valid Excel date format for the start_date. Invalid dates result in a #VALUE! error.
  • Remember, EOMONTH returns the date as a serial number, so you might need to format the cell for a date display.

Related Functions

Excel TODAY function

The Excel TODAY function returns the current date, ideal for daily updates without including time.

Excel EDATE function

The Excel EDATE function calculates dates a specific number of months away, aiding in financial planning and scheduling tasks.

Content Navigation