SheetsLessons
Lesson 41 · Dates & Times

EDATE & EOMONTH

Practise EDATE and EOMONTH in Google Sheets: renewal dates, end-of-month due dates, the first day of a month and the number of days in a month.

Task 1 of 5

Each subscription runs for the number of months in column B. Return the first one's end date in C2.

C2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

Moving by whole months

Adding 30 to a date doesn't give "one month later", because months have different lengths. Two functions handle month maths properly:

  • =EDATE(start_date, months) returns the same day of the month, months later (or earlier, with a negative number). If that day doesn't exist, it uses the last day of the month: one month after January 31 is February 28.
  • =EOMONTH(start_date, months) returns the last day of the month, months away. Use 0 for the end of the same month.

Handy patterns

You wantFormula
Contract end, 12 months after the start=EDATE(A2, 12)
Last day of this month=EOMONTH(A2, 0)
Payment due at the end of next month=EOMONTH(A2, 1)
First day of this month=EOMONTH(A2, -1)+1
Days in this month=DAY(EOMONTH(A2, 0))