FormuladrillEOMONTH and EDATE
EOMONTH(start_date, months) returns the last day of the month that is a given number of months away (0 for the current month), and EDATE(start_date, months) returns the same day of the month a number of months later or earlier. Both return a date serial number, so format the cell as a date.
Syntax
EOMONTH(start_date, months) EDATE(start_date, months)| Part | What it is |
|---|---|
start_date | A real date or a cell holding one. |
months | Months to move: 0 for this month, 1 for next, -1 for last. Decimals are truncated. |
Examples
=EOMONTH(DATE(2026, 2, 10), 0)Result: 28 February 2026
Last day of the same month. 2026 is not a leap year.
=EOMONTH(A2, 1)Result: The last day of the month after the invoice date in A2, a common payment term
=EDATE(DATE(2026, 1, 31), 1)Result: 28 February 2026
When the target month is shorter, EDATE returns its last day instead of overflowing.
=EOMONTH(A2, -1) + 1Result: The first day of the month of A2
End of the previous month plus one day.
Common mistakes
- The cell shows a number like 46446: the result is a date serial. Apply a date format.
- #VALUE!: start_date is text such as "2026-02-10" in a locale that does not parse it. Use a real date or DATEVALUE.
- Adding 30 days instead of a month: A2 + 30 drifts across the year. EDATE(A2, 1) keeps the day of month.
- Expecting EDATE(31 Jan, 1) to be 3 March: it returns 28 February because it clamps to the end of the shorter month.
Practice EOMONTH and EDATE
These drills check your answer on live data, free, no account needed.
- Pay by month endInvoices are due on the last day of the month after the invoice date. Fill B2 and B3 with EOMONTH.
- Contract end dateEach contract runs for the number of months in column C. Return the end date in D2 and D3 with EDATE.
Questions
How do I get the last day of the month in Excel?
=EOMONTH(A2, 0). For the last day of next month use 1, for last month use -1.
How do I add months to a date in Excel?
=EDATE(A2, 3) adds three months. Negative numbers subtract: =EDATE(A2, -6).
How do I get the first day of the month?
=EOMONTH(A2, -1) + 1, or =DATE(YEAR(A2), MONTH(A2), 1).
Related
- DATE and DATEDIFHow the Excel DATE function builds a date from year, month and day, and DATEDIF counts complete years, months or days between two dates, plus an age formula.
- NETWORKDAYS and WORKDAYHow NETWORKDAYS counts the working days between two dates and WORKDAY adds working days, skipping weekends and holidays, and .INTL for other weekends.
- TODAY and NOWHow the Excel TODAY and NOW functions insert the current date or date and time, with formulas for days until a deadline, age, overdue flags and fixed dates.
- PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.