EOMONTH is a function in Excel which will find the last date of a month based on a specified number of months before or after a given date, or for the current month.
The function can also be used to calculate the start date of future or past months by changing the number of months you are searching for and then adding in additional days.
Download File
If you would like to follow along download the attachment below.
What is the EOMONTH Function?
The EOMONTH function returns the last day of the month based on a specified starting date. You can use it to find the end of the month that’s before or after a given number of months, or for the current month.
The syntax of the EOMONTH function is:
=EOMONTH(start_date, months)
start_date: This is the initial date from which you want to calculate the last day of the month.
months: This indicates the number of months before or after the start date. A positive number looks forward, a negative number looks backward, and a zero shows the current month.
Notes on Formatting
Excel treats all dates as serial numbers that can be formatted to appear in different ways.
For example the serial number 45580 represents October 15th 2024 which can then be formatted to appear as 15/10/2024,15-October-24 or a number of other ways.
The date outputs in the examples below have been formatted to show as day/month/year, but depending on how you have your cells formatted the default output for EOMONTH may be the dates serial number rather than a date format.
If this is the case then you can simply change the output to show in the day/month/year format by going to the Home tab and selecting the drop down from the Number section.
When you click the drop down in the Number section you can select Short Date from the drop down list.
To learn more about changing date formats and why Excel uses as serial number for dates click here.
Using EOMONTH to Calculate the End of the Month
As its default setting EOMONTH will take the month of the date you input and find the last date of the month however many months ahead or behind you have specified in the months parameter.
Leap Years
EOMONTH automatically takes leap years into consideration and will know whether to treat February as 28 or 29 days depending on the year.
Using EOMONTH to Calculate the Start of the Month
There is no function in Excel to calculate the start of the month but it is possible to do this using EOMONTH and adding on a day.
The way to do this is to enter the month prior to the month you want the start of in the months section of the EOMONTH to get the last day of the prior month, then add 1 day to get the first day of the next month.
Conclusion
The EOMONTH function can help to automatically find the end of month a given number of months in the future, past or current month.
While there is no function to find the first date of a month a specific number of months in the future or the past, you can use EOMONTH to help find the first date too by selecting one month prior to the month you want to start from then adding one day.
Comments