By default, the EOMONTH function returns a date serial number. So, before you use it, be sure to apply a date number format to the relevant cells.
The EOMONTH function requires two arguments:
=EOMONTH(a,b)
where argument a is the start date (any day of any month in any year), and argument b is the number of months before or after the start date. After taking the start date and moving forward or backward the specified number of months, Excel returns the last date of the month it lands on.
Argument a can be a reference to a cell containing the start date, a start date entered using the DATE function, or a start date generated using another function, such as the TODAY function.
Argument a | Description |
---|---|
=EOMONTH(A1, | The start date is the date entered in cell A1. |
=EOMONTH(DATE(2025,05,31), | The start date is May 31, 2025. |
=EOMONTH(TODAY(), | The start date is today’s date. |
For argument b, a positive number represents the number of months after the start date, a negative number represents the number of months before the start date, and zero stays in the same month. You can hard-code this argument manually, or reference a cell containing the number of months you want to jump backward or forward:
Calculating future Month-End Dates
The most common use of the EOMONTH function in Excel is to return the last day of a month in the future. Imagine you’re the project manager of a construction company, and you’ve assigned projects to ten employees. Each project takes a certain length of time to complete, and completion deadlines are at the end of the month.
So, to calculate the end dates in column E, you need to use the EOMONTH function.
In cell E2, first type:
=EOMONTH(
Then, select the cell containing the start date and add a comma:
=EOMONTH([@[Start date]],
Finally, select the cell containing the number of months to jump forward, close the parentheses, and press Enter:
=EOMONTH([@[Start date]],[@Months])
If your data is formatted as an Excel table, as in the example screenshots, the formula will use structured references to the column headers, and the column will autocomplete when you enter the formula into the first cell. On the other hand, if your data is a regular range, it will use direct cell references, and you’ll need to use the fill handle to apply the formula to the remaining cells in the column.
There are two benefits to using cell references instead of hard-coding the date and number of months. First, the formula is correct according to the data on each row, and second, if you need to change the time period or start date of a given project, you can simply modify the values in columns B or D without editing the formula.
Calculating Past Month-End Dates
The EOMONTH function can also tell you the month-end date of a month that is a certain number of months before a start date. Confused? Let me show you a real-world example. In this example, you have a table of ten projects, and you want to work out the latest point at which your employees must start them to meet the deadline in column E.
Specifically, if a project takes five months, you want to return the month-end date of the month that is six months before the deadline, leaving some extra room for any problems that arise in the process. To do this, in cell D2, type the following formula and press Enter:
=EOMONTH([@Deadline],-[@Months]-1)
So, for the project in row 2, the EOMONTH takes the deadline date of January 27, 2026, jumps back five months to August 2025, and returns the month-end date of the previous month—July 31, 2025. As a result, if Mark, the assignee, starts the project on July 31, 2025, he should complete the five-month project with a few days to spare, provided everything goes to plan.
Calculating Long-Term Past and Future Month-End Dates
The EOMONTH function in Excel is great for jumping a given number of months forward or backward from a start date, before returning the month-end date. However, what if you want to jump forward or backward a given number of years? Well, EOMONTH can take care of this, too.
Take this example, where you want to return the end date for each long-term project based on a start date and a year-based timescale. Specifically, the end date must be the end of a given month since this is when you like to review the progress of your projects.
So, in cell E2, type:
=EOMONTH([@[Start date]],[@Years]*12)
Here’s what you get when you press Enter:
So, for Mark’s project in row 2, the EOMONTH formula used above takes the start date of January 4, 2025, jumps two years (24 months) forward to January 4, 2027, and returns the month-end date of January 31, 2027.
To jump back a given number of years, place a minus symbol (-) before the second argument. For example:
=EOMONTH([@[Start date]],-[@Years]*12)
Calculating the Month-End Date of the Current Month
While Excel’s EOMONTH function is mainly used to calculate the end date of a month in the past or future, it can also return the month-end date of the current month.
Suppose you’ve created an Excel workbook, where each month’s budget is on a separate worksheet. As well as using checkboxes and the SUMIF function to work out completed and upcoming expenditures, you want to use the EOMONTH function to calculate the number of days remaining in the month and, thus, the maximum amount you can spend each day.
First, in cell B2, you need to create a formula that subtracts today’s date from the month-end date. This will return the number of days left in the month. To do this, type:
=EOMONTH(TODAY(),0)-TODAY()
where
- EOMONTH(TODAY(),0) returns the serial number of the last date in the current month,
- TODAY() returns the serial number of the current date, and
- The minus symbol subtracts the latter from the former.
Since you’re dealing with dates and serial numbers, Excel may convert the result to a date, where, in fact, you want it to return the number of days. If so, select the cell, and choose the “General” number format in the drop-down menu of the Number group in the Home tab on the ribbon.
Now, you can calculate how much you can spend each day in cell B7:
=B6/B1
Generating Sequences of Month-End Dates
Combining functions in Excel can take your spreadsheet to the next level, and a good example of this is nesting the SEQUENCE function in an EOMONTH formula to return a list of month-end dates.
The SEQUENCE function, which acts as the second argument of the EOMONTH function, has four arguments:
=SEQUENCE(a,b,c,d)
where
- a is the number of rows to return,
- b is the number of columns to return,
- c is the first number of the sequence, and
- d is the increment between each value in the sequence.
=EOMONTH(DATE(2024,12,31),SEQUENCE(12))
The SEQUENCE function tells Excel to return a 12-row sequence, and the EOMONTH function defines the sequence as an array of month-end dates starting the month after December 31, 2024.
The SEQUENCE function returns a dynamic array, meaning the result spills from the cell where you typed the formula to adjacent cells. Dynamic array functions aren’t compatible with Excel tables, so the nested formula you type must be in a regular cell.
Let’s say you want to return the month-end dates for every second month—rather than every month—in 2025, starting January 31. In this case, type:
=EOMONTH(DATE(2024,12,31),SEQUENCE(6,,,2))
where the first argument of the SEQUENCE formula tells Excel to return six rows of results, and the fourth argument represents a two-month increment between each value in the sequence. Finally, if you wanted to return quarterly month-end dates, you would type:
=EOMONTH(DATE(2025,2,28),SEQUENCE(4,,,3))
To fix the dates once you’ve used the EOMONTH-SEQUENCE combination, select all the cells containing the dates, press Ctrl+C to copy them, and press Ctrl+Shift+V to paste the values only. Then, because the values are no longer in a dynamic array, you can format the cells as an Excel table.
As you’ve seen in this guide, EOMONTH jumps forward or backward a given number of months and returns the last day of the resultant month. However, if you want to return the same day of the resultant month, use the EDATE function instead.
[mai mult...]