Dates, Time & Schedules
Generate a Rolling Sequence of Month-Start & Month-End Dates (EDATE & EOMONTH)
Step forward or backward any number of calendar months from a start date using EDATE(DATE(Year, Month, 1), Offset) for exact 1st-of-month headers and EOMONTH(..., Offset) for leap-year-safe month-end closing dates.
Never add "+ 30" days to build monthly accounting schedules!
Adding +30 or +31 days to step through calendar months drifts across 28-day Februaries, 29-day leap Februaries, and 31-day summer months—turning January 31 into March 2 and corrupting monthly SUMIFS cutoffs!
Spreadsheet date engines provide two dedicated month-stepper functions that respect exact calendar month lengths and year rollovers automatically: EDATE(DATE(Start_Year, Start_Month, 1), Month_Offset) advances by Month_Offset months to land on the exact 1st of every target month (so Offset = 0 -> 2026-01-01, Offset = 1 -> 2026-02-01, Offset = 13 -> 2027-02-01), while EOMONTH(DATE(Start_Year, Start_Month, 1), Month_Offset) lands on the exact final calendar day of that month (2026-02-28 in a non-leap year vs. 2028-02-29 in a leap year)!
| Month Offset k (from Jan 2026) | Month-Start Formula EDATE(..., k) | Month-End Formula EOMONTH(..., k) |
|---|---|---|
| k = 0 (Anchor Month) | 2026-01-01 | 2026-01-31 |
| k = 1 (Next Month — Feb 2026) | 2026-02-01 | 2026-02-28 (28-day Feb) |
| k = 25 (Leap Year Feb 2028) | 2028-02-01 | 2028-02-29 (29-day Leap Feb!) |
ISO Month-Start Date at Offset k Months: EDATE(DATE(Year, Month, 1), k)
Computes the exact 1st-of-month date k months from Start_Year (B2) and Start_Month (B3) formatted as yyyy-mm-dd.
=IF(OR(B2 < 1900, B3 < 1, B3 > 12), "Invalid Start Month", TEXT(EDATE(DATE(B2, B3, 1), B4), "yyyy-mm-dd"))ISO Month-End Closing Date at Offset k Months: EOMONTH(DATE(Year, Month, 1), k)
Returns the exact 28th, 29th, 30th, or 31st closing date of the k-th forecast month.
=IF(OR(B2 < 1900, B3 < 1, B3 > 12), "Invalid Start Month", TEXT(EOMONTH(DATE(B2, B3, 1), B4), "yyyy-mm-dd"))Try the example
Use the original values or change them to test your understanding.