If you want to add or subtract whole months from a date, the EDATE function in Google Sheets does it in one step. You pass it a starting date and a month count, and it lands on the same calendar day that many months away.
In this article, I’ll show you how to use EDATE to move dates forward and step them back. I’ll also cover the end-of-month clip behavior, how to calculate work anniversaries, and how to compute subscription end dates.
EDATE Function Syntax in Google Sheets
The EDATE function returns a date that is a given number of months before or after a start date.
=EDATE(start_date, months)
- start_date is the date you want to shift from. Usually a cell reference like A2.
- months is the number of whole months to add. Pass a positive number to move forward, a negative number to move backward.
EDATE returns a date serial number. By default the result cell shows the raw number, like 45397. Format the cell as a date (Format menu, then Number, then Date) to see it as Apr 15, 2024.
When to Use EDATE Function
- Shift a date forward by any number of months without doing the calendar math by hand.
- Step a date backward to find what happened a few months earlier.
- Compute a renewal or expiry date from a start date and a term length.
- Calculate work anniversaries by adding multiples of 12 months to a hire date.
- Get the last valid day of a future month when the start date is the 31st.
Example 1: Shift a Start Date Forward by a Number of Months
Let’s start with the most common use, moving a date forward by some number of months.
Below is the dataset. Column A has the start date and column B has the number of months to add.
I want the date that lands that many months after each start date.
Here is the formula:
=EDATE(A2, B2)
EDATE takes the start date in column A and adds the month count in column B. The first row, 2024-01-15 plus 3 months, lands on 2024-04-15. The second row, 2024-03-30 plus 1 month, lands on 2024-04-30.
The cell shows a serial number like 45397 because EDATE returns the raw date value. Highlight column C, open the Format menu, pick Number, then Date, and the serial flips to a readable date.
Pro Tip: If you want the last day of the target month instead of the same calendar day, use EOMONTH. EDATE keeps the day number where possible. EOMONTH always lands on month-end.
Example 2: Step Backwards by Passing a Negative Month Count
EDATE works in both directions. A negative month count walks the date backward.
Below is the dataset. Column A has the start date and column B has a negative number of months.
I want the date that sits that many months before each start date.
Here is the formula:
=EDATE(A2, B2)
The first row, 2024-06-15 minus 3 months, lands on 2024-03-15. The last row, 2024-12-25 minus 12 months, lands on 2023-12-25 (exactly one year earlier).
This comes in handy for “what was the date X months ago” calculations, like working out when a 6-month-old invoice was issued or when a contract started given today’s expiry.
Example 3: End-of-Month Dates Clip to the Target Month
This is the EDATE quirk worth knowing. When the start date is the 31st, EDATE returns the last valid day of the target month rather than overflowing.
Below is the dataset. Column A has dates that fall on the 31st of various months, and column B has the month shift.
I want to see how EDATE handles each shift when the target month does not have 31 days.
Here is the formula:
=EDATE(A2, B2)
Look at the first two rows. 2024-01-31 plus 1 month lands on 2024-02-29, because February 2024 is a leap year and the 29th is its last day. 2023-01-31 plus 1 month lands on 2023-02-28, because February 2023 only goes up to the 28th.
Rows three and four show the same behavior shifting into April and June. 2024-03-31 plus 1 lands on 2024-04-30, and 2024-05-31 plus 1 lands on 2024-06-30. The last row, 2024-01-31 plus 13, lands on 2025-02-28.
EDATE never spills into the following month. It clips to whatever the last valid day is. Good to know if your data starts on month-ends and you need the result to behave predictably.
Example 4: Calculate a Work Anniversary From a Hire Date
A simple way to compute anniversaries is to add multiples of 12 to a hire date.
Below is the dataset. Column A has the employee name, column B has the hire date, and column C has the number of months to add (12 for year one, 24 for year two, and so on).
I want the anniversary date in column D.
Here is the formula:
=EDATE(B2, C2)
Emily Carter, hired on 2020-03-15, hits her one-year mark on 2021-03-15. Aarav Sharma, hired 2019-07-22, hits two years on 2021-07-22. Each row adds its month count to the hire date and lands on the same calendar day in a later year.
Because the month count is always a multiple of 12, the day and month stay the same. Only the year changes. Same trick works for service-award years, contract renewals, or any yearly milestone.
Example 5: Compute a Subscription End Date From Term Length
Subscriptions usually have a start date and a term in months. EDATE turns those into an end date.
Below is the dataset. Column A has the customer name, column B has the start date, and column C has the term in months.
I want the end date in column D, computed as the start date plus the term.
Here is the formula:
=EDATE(B2, C2)
Jacob Mitchell’s 6-month subscription starts 2024-01-15 and ends 2024-07-15. Sophia Nguyen’s 12-month plan runs from 2024-02-01 to 2025-02-01. Noah Bennett’s 24-month plan starts 2024-05-15 and ends 2026-05-15.
This pattern shows up everywhere: software trials, gym memberships, lease terms, warranty windows. Pass EDATE the start date and the term in months, and you have the end date.
Tips & Common Mistakes
- Format the result column as a date. EDATE returns a serial number by default, like 45397. Highlight the column, open Format, pick Number, then Date, and the serial becomes a readable date. Skipping this step trips up most first-time users.
- End-of-month clips, it does not roll over. Adding 1 month to 2023-01-31 gives 2023-02-28, not a date in March. If you specifically want the last day of the target month every time, use EOMONTH instead.
- Negative months step backward. Pass a negative value as the second argument to move the date earlier, useful for “X months ago” calculations. The same end-of-month clipping rule applies in both directions.
EDATE is the cleanest way to add or subtract whole months from a date in Google Sheets.
You’ve now seen it shifting forward, stepping back, clipping at month-end, computing anniversaries, and ending subscriptions. Pass it a date and a month count, format the result as a date, and you’re done.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: