EOMONTH and EDATE for Model Timelines

EOMONTH in Excel returns the last day of a month a set number of months away from a start date, which makes it the workhorse for period headers in a model. EDATE returns the same day of the month a set number of months out, which is ideal for anniversary dates. Both take a positive months argument to move forward and a negative one to move backward.

How EOMONTH and EDATE work

=EOMONTH(start_date, months) returns a serial date for the last day of the month that is months away from start_date. With months set to 0 it returns the end of the start date's own month, so =EOMONTH("2026-03-15", 0) gives 2026-03-31.

=EDATE(start_date, months) returns the same day of the month, months away. So =EDATE("2026-01-31", 1) returns 2026-02-28, because February has no 31st and EDATE clamps to the month end.

Both functions return a serial number. Format the result cell as a date so it reads as 2026-03-31 rather than 46112. A positive months argument moves forward in time and a negative one moves backward.

Worked example: a monthly forecast header row

Suppose your forecast starts in January 2026 and you want a row of month-end dates running across the columns.

1. In B1 enter the model start date, 2026-01-01.
2. In B2 write =EOMONTH(B1, 0) to get the first period end, 2026-01-31.
3. In C2 write =EOMONTH(B2, 1) and drag it right; each cell steps to the next month end, giving 2026-02-28, 2026-03-31, and so on.
4. For a same day anniversary instead, write =EDATE(B1, 12) to get 2027-01-01, exactly one year out.
5. Format the row as a custom date like mmm-yy so the header reads Jan-26, Feb-26, Mar-26.

Chaining EOMONTH off the prior cell keeps every header on its true month end.

How analysts use them in models

Clean, formula-driven dates are the backbone of a maintainable model timeline.

• Build period headers once with EOMONTH so the whole forecast reflows when you change the start date in one input cell.
• Use EDATE to set debt maturity, contract renewal, or depreciation start dates a fixed number of months from an event.
• Combine EOMONTH with the day count between headers to derive period lengths for daily interest or proration.
• Use a negative months argument, like =EOMONTH(B1, -1), to grab the prior month end for opening balances.
• Wrap EDATE in your forecast to roll a deal close date forward by quarters using multiples of 3.

Pitfalls to watch

A raw EOMONTH or EDATE result looks like a five digit number until you apply date formatting. The value is correct; only the display is missing the format.

EDATE preserves the day of the month, so chaining it off a 31st can drift. Starting at 2026-01-31 and adding 1 gives 2026-02-28, and adding another gives 2026-03-28, not 2026-03-31. Use EOMONTH when you specifically want month ends.

Typing a date as a text string inside the function, such as =EOMONTH("Jan 2026", 0), is fragile across regional settings. Reference a real date cell instead so the timeline does not break on another machine.