AI Excel Generator
← ALL GUIDES

How to Add Months to a Date in Excel

Updated November 4, 2026 · AI Excel Generator

To add months to a date in Excel, use the EDATE function, which returns a date the requested number of months before or after a starting date.

Its handling of short months can affect the result, so the examples below show what to expect and how to adapt the formula.

Use EDATE to add months to a date

The EDATE function shifts a date by a number of whole months while keeping the day of the month when possible.

Its syntax is:

=EDATE(start_date, months)

  • start_date is a valid Excel date or a cell containing one.
  • months is the number of months to move.

    Use a positive number to move forward and a negative number to move backward.

For example, if cell A2 contains January 15, 2026, =EDATE(A2,3) returns April 15, 2026.

If A2 contains January 15, 2026, then =EDATE(A2,-2) returns November 15, 2025.

Excel stores dates as serial numbers, so a result may display as a number instead of a date.

Select the result cell and choose a date format from Home > Number, or use Ctrl+1 to open Format Cells.

Formatting changes how the result appears; it does not change the underlying date.

What happens when the target month has fewer days?

When the target month does not have the original day number, EDATE returns the last day of the target month.

For example, =EDATE(DATE(2026,1,31),1) returns February 28, 2026.

The same formula starting from January 31, 2024 returns February 29, 2024, because 2024 is a leap year.

This behavior is useful for month-end schedules: adding one month to a date at the end of a long month lands on the target month's final day.

It also means that repeated monthly calculations can differ from adding a larger month offset directly.

If A2 contains January 31, 2026, =EDATE(A2,2) returns March 31, while adding one month and then another to the intermediate February date may land on March 28.

For recurring dates, calculate each period from the original anchor date when you want to preserve its intended schedule.

Calculate a date from a cell or a fixed month count

If A2 contains a starting date and B2 contains the number of months, use:

=EDATE(A2,B2)

For instance, if A2 is March 10, 2026, and B2 is 6, the formula returns September 10, 2026.

A negative value in B2 moves the date into the past.

You can fill the formula down a column to calculate dates for multiple rows.

To add a fixed number of months to every date, put that number directly in the formula.

For example, =EDATE(A2,12) adds one year, and =EDATE(A2,18) adds a year and a half.

The months argument must be numeric; if it refers to a cell containing text such as “six,” Excel may return a #VALUE! error.

Find the first or last day of a month

EDATE is useful for creating billing periods, reporting dates, and monthly schedules.

To return the first day of the month a given number of months after A2, use:

=EOMONTH(A2,B2-1)+1

With A2 set to January 15, 2026, and B2 set to 2, this returns March 1, 2026.

To find the last day of the target month, use:

=EOMONTH(A2,B2)

For the same date and month count, this returns March 31, 2026.

EOMONTH and EDATE both accept positive or negative month offsets, making them useful for calculating period boundaries.

Can you add months without EDATE?

If EDATE is unavailable in your spreadsheet environment, a DATE formula can shift a date by whole months:

=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))

For most ordinary dates, this produces the expected month and year.

However, DATE normalizes an invalid day into the following month instead of clamping to the target month's end.

For example, adding one month to January 31 with this formula can roll into March.

To match EDATE's month-end behavior, use this alternative:

=DATE(YEAR(A2),MONTH(A2)+B2,MIN(DAY(A2),DAY(EOMONTH(DATE(YEAR(A2),MONTH(A2)+B2,1),0))))

The longer formula finds the final day of the target month and uses the smaller of that day number and the original day number.

This preserves January 31 as February's final day instead of rolling into March.

Compatibility and common errors

EDATE and EOMONTH are available in modern desktop versions of Excel, Excel for the web, and Google Sheets.

In some older Excel versions, the functions may require the Analysis ToolPak add-in.

If Excel reports that the function name is invalid, check your version and add-in settings.

  • #VALUE!: The start date or month count may be stored as text or may not be a valid value.

    Check the source cells and convert text dates to real dates.
  • A serial number appears: Apply a date number format to the formula cell.
  • The result is unexpected near month-end: Check whether you want EDATE's last-day behavior or DATE's rollover behavior.
  • The formula displays instead of calculating: The cell may be formatted as Text, or the formula may begin with an apostrophe.

    Change the format to General, then re-enter the formula.

Regional settings can affect formula separators: some installations use semicolons instead of commas.

If Excel rejects =EDATE(A2,3), try =EDATE(A2;3).

Also confirm that the input is a genuine date value rather than text that only looks like a date.