EOMONTH returns the last day of a month, a specified number of months before or after a start date. It is ideal for calculating month-end dates for deadlines, reporting periods, and financial schedules.
=EOMONTH(start_date, months)What does EOMONTH do?
EOMONTH is a Date & Time function that gives you the last day of a month. You provide a start date and a number of months, and Excel returns the final date of the resulting month. For example, =EOMONTH(DATE(2024,1,15),0) returns 31 January 2024, and =EOMONTH(DATE(2024,1,15),1) returns 29 February 2024.
Month-end dates matter in almost every office job. Invoices are due at month end, payroll closes on the last working day, reports cover full calendar months, and contracts renew on the final day of a period. Calculating those dates by hand is slow and error-prone, especially when months have different lengths or you cross a year boundary. EOMONTH handles all of that automatically, including leap years.
A common real situation: you manage a list of invoices with issue dates and payment terms of 30 days. Instead of adding 30 days and guessing the due date, you can use EOMONTH to set every due date to the last day of the following month. That keeps your cash-flow forecast consistent and easy to audit.
When to use EOMONTH
- Calculating invoice due dates that fall at month end.
- Setting payroll or accounting close dates for each month.
- Building monthly report periods that always end on the last day.
- Finding contract renewal or subscription expiry dates at month end.
- Creating date ranges for financial quarters or fiscal periods.
- Automating reminders for month-end tasks in a project tracker.
EOMONTH syntax
=EOMONTH(start_date, months)| Argument | Required? | What to enter |
|---|---|---|
start_date | Required | The date you want to base the calculation on. It can be a cell reference, a date entered with DATE, or a text date that Excel recognises. Example: A2 where A2 contains 2024-03-15. |
months | Required | The number of months before or after start_date. Use 0 for the same month, a positive number for future months, and a negative number for past months. Example: 1 for next month, -1 for last month. |
What it returns: A serial number that Excel displays as a date: the last day of the month that is the specified number of months from start_date. The result is always a valid date, such as 2024-02-29 for a leap year.
EOMONTH examples
1 Invoice due date at the end of the following month
A business issues invoices with payment terms of 30 days after the end of the following month. The formula uses EOMONTH with months equal to 1 to get the last day of the month after the invoice date. For an invoice dated 12 November 2024, it returns 31 December 2024. This approach gives every invoice a consistent month-end due date and avoids manual date maths.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice ID | Invoice Date | Customer | |||
| 2 | INV-1001 | 2024-11-12 | Northwind Traders | 2024-12-31 | ||
| 3 | INV-1002 | 2024-11-28 | Contoso Ltd | |||
| 4 | INV-1003 | 2024-12-03 | Fabrikam Inc | |||
| 5 |
2 Payroll close date for the current month
Payroll often closes on the last day of the month. This example uses EOMONTH with months equal to 0 to return the month-end date for each pay period. For a pay period starting 1 May 2024, the close date is 31 May 2024. The formula is simple, but it removes the risk of typing the wrong month length, especially for February.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Pay Period Start | Department | |||
| 2 | 2024-05-01 | Finance | 2024-05-31 | ||
| 3 | 2024-06-01 | Operations | |||
| 4 | 2024-07-01 | Sales | |||
| 5 |
Common EOMONTH errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | start_date is text that Excel cannot interpret as a date, or months is not a number. | Make sure start_date is a valid date (use DATE or a real date cell) and months is a number. If dates are stored as text, convert them with DATEVALUE or Text to Columns. |
| #NUM! | The resulting date is outside Excel's supported date range (before 1 January 1900 or after 31 December 9999). | Check that start_date and months produce a date within the allowed range. Reduce the size of months or correct the start date. |
| #NAME? | The function name is misspelled, for example EOMONTH written as EOMONTHH or with a missing parenthesis. | Type =EOMONTH( and let Excel suggest the function, or correct the spelling. |
| Wrong date shown as a number | The cell is formatted as General or Number, so Excel shows the date serial number instead of a date. | Select the cell and apply a Date format from the Home tab, or use Format Cells and choose Date. |
Tips for using EOMONTH
- Use
months= 0 for the current month, 1 for next month, and -1 for last month. - Combine EOMONTH with TODAY to get the current month end:
=EOMONTH(TODAY(),0). - To get the first day of the next month, use
=EOMONTH(A2,0)+1. - EOMONTH automatically handles leap years, so February returns 29 days in leap years.
- If your dates are stored as text, convert them to real dates first to avoid #VALUE! errors.
- Use a custom date format such as
dd mmm yyyyto make month-end dates easy to read.
Frequently asked questions
What is the difference between EOMONTH and EDATE?
EDATE returns the same day number in a future or past month, while EOMONTH always returns the last day of that month. For example, EDATE(2024-01-15,1) returns 2024-02-15, but EOMONTH(2024-01-15,1) returns 2024-02-29.
Can EOMONTH return the first day of a month?
Not directly. EOMONTH returns the last day. To get the first day of the next month, add 1 to the result: =EOMONTH(A2,0)+1. To get the first day of the current month, use =EOMONTH(A2,-1)+1.
Why does EOMONTH show a number instead of a date?
The cell is formatted as a number. Apply a Date format to the cell, and the serial number will display as a date.
Does EOMONTH work with dates entered as text?
It can work if Excel recognises the text as a date, but it is safer to store dates as real dates. If you see #VALUE!, convert the text with DATEVALUE or use Text to Columns.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. EOMONTH reference