Last Day of the Month in Excel - EOMONTH function
To get the date of the last day of the month in Excel use the EOMONTH (End of Month) function.
1. The EOMONTH function below returns the last day of the month.
Note: the EOMONTH function returns the serial number of the date. Apply a Date format to display the date.
2. The EOMONTH function below returns the same date. Use the DATE function in Excel to hardcode a date.
Let's kick it up a notch! Use the second argument of the EOMONTH function to specify a number of months in the future or in the past.
3. The EOMONTH function below returns the last day of the next month.
4. The EOMONTH function below returns the last day of the previous month.
5. The EOMONTH function below returns the last day of the month 8 months in the past!
Explanation: = 6 - 8 = -2 = October (-2 + 12 = 10) in 2023!
6. Use EOMONTH and TODAY to return the last day of the current month.
Note: today is August 15th 2024.
7. Finally to find the first day of the month in Excel simply use DATE YEAR and MONTH.
Explanation: the DATE function accepts three arguments: year month and day. Use YEAR to get the year of a date. Use MONTH to get the month of a date.
Written by
Jason Howie
Founder, FormulasHQ
A formula nerd with a passion for numbers and equations. Writes about Excel, Google Sheets, VBA, regex, and Salesforce formulas — for people who spend their day in spreadsheets.