ThisCalculator

Everyday

Excel Formula Add Months to Date: EDATE Guide

Updated · Standard formula, shown below

Excel Formula Add Months to Date: EDATE Guide

The simplest Excel formula to add months to a date is =EDATE(start_date, months). You enter the original date and the number of months to add (or subtract with a negative number), and Excel returns the adjusted date.

Formula

The EDATE function has two arguments:

  • start_date – the date you want to begin from. This can be a cell reference, a date entered with the DATE function (e.g., DATE(2024,5,15)), or a date returned by another formula.
  • months – the number of months to add. Use a positive number to go forward, a negative number to go backward, and zero to keep the same date.

In words: EDATE(start_date, months) returns the same day of the month, but shifted by the specified number of months. If the start date is January 31, adding one month gives February 28 (or February 29 in a leap year), because February has no 31st.

Worked Example

Suppose cell A1 contains 2024-01-15 and you want to add 3 months. The formula is:

=EDATE(A1, 3)

Excel calculates this as:

  1. Start with the date: January 15, 2024.
  2. Add 3 months to the month number: January (1) + 3 = April (4).
  3. Keep the day as 15.

The result is April 15, 2024. If you enter the date directly in the formula, use =EDATE(DATE(2024,1,15), 3).

When to Use EDATE vs. Simple Addition

Many users try =A1+30 to add a month, but that adds 30 days, not one calendar month. This works for some dates but fails across months of different lengths. For example, January 31 + 30 days gives March 2 (in a non-leap year), not February 28. EDATE always respects the actual month length.

Use EDATE when you need to:

  • Calculate due dates, subscription renewals, or payment schedules that fall on the same day each month.
  • Shift a date by a whole number of months without worrying about varying days per month.
  • Subtract months (e.g., =EDATE(A1, -6) for six months prior).

For adding a fixed number of days (e.g., 30, 45, 90), use simple addition: =A1+30.

How to Format the Result as a Date

After entering the formula, Excel might show a number like 45232 instead of a date. That is because the cell is formatted as General. To fix it:

  1. Select the cell with the formula.
  2. Press Ctrl+1 (or right-click → Format Cells).
  3. Under the Number tab, choose Date and pick a format (e.g., MM/DD/YYYY or March 14, 2024).
  4. Click OK.

Alternatively, use the Home tab → Number Format dropdown and select a date format.

Common Mistakes and How to Avoid Them

  • Not formatting as date – If you see a large number, the cell is not date-formatted. Apply a date format.
  • Entering text instead of a date – If you type “Jan 15, 2024” as text, EDATE may return a #VALUE! error. Use a cell with a real date or wrap the text in DATEVALUE: =EDATE(DATEVALUE("1/15/2024"), 3).
  • Adding days instead of months – Using =A1+30 when you mean one month will give wrong results across month boundaries.
  • Forgetting negative numbers for subtraction – To go back in time, use a negative number: =EDATE(A1, -3).
  • Using the wrong day for end-of-month dates – EDATE returns the last day of the target month if the original day doesn’t exist. This is correct behavior, but be aware of it.

Related Functions: DATE, YEAR, MONTH, DAY

Sometimes you need more control than EDATE offers. For example, to add months and also change the day, you can combine functions:

=DATE(YEAR(A1), MONTH(A1)+3, DAY(A1))

This adds 3 to the month and lets Excel handle the day overflow. However, if the day is 31 and the target month has fewer days, Excel rolls over to the next month (e.g., Jan 31 + 3 months = May 1). EDATE, in contrast, clamps to the last day of the target month (April 30). Choose based on your need.

Other useful functions:

  • EOMONTH(start_date, months) – returns the last day of the month, months away. For example, =EOMONTH(A1, 1) gives the last day of the next month.
  • DATE(year, month, day) – builds a date from parts.
  • YEAR(date), MONTH(date), DAY(date) – extract parts of a date.

Frequently Asked Questions

How do I add months to a date and keep the day the same?

Use =EDATE(start_date, months). It keeps the day the same, except when the target month has fewer days (e.g., Jan 31 → Feb 28). If you want to force the day to stay exactly the same even if it rolls over, use =DATE(YEAR(A1), MONTH(A1)+n, DAY(A1)), but that may give a date in the following month.

What if my date is text, not a real date?

Convert it first with =DATEVALUE("text date") or use the DATE function. For example, =EDATE(DATEVALUE("1/15/2024"), 3). Otherwise, EDATE will return a #VALUE! error.

Can EDATE add years?

Yes, by multiplying years by 12. For example, =EDATE(A1, 2*12) adds 2 years. Or use =DATE(YEAR(A1)+2, MONTH(A1), DAY(A1)).

Why does my EDATE result show as a number?

Because the cell is formatted as General. Change the cell format to Date (Ctrl+1 → Date). Excel stores dates as serial numbers, so without date formatting you see the underlying number.

How do I add months to a date in Excel for free?

Excel formulas work the same in Microsoft 365, Excel 2019, and older versions. There is no cost for using EDATE; it is a built-in function. If you are using Google Sheets, the same formula works.

What is the difference between EDATE and EOMONTH?

EDATE returns the same day of the month (or the last day if the day doesn’t exist). EOMONTH always returns the last day of the target month. For example, from Jan 15, EDATE(…,1) gives Feb 15, while EOMONTH(…,1) gives Feb 28 (or 29).

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.