EDATE Function – Add or Subtract Months to a Date (Excel, Sheets, VBA)

This tutorial demonstrates how to use the Excel EDATE Function in Excel to add or subtract months to a date.

EDATE main function

EDATE Function Overview

The EDATE Function Returns a date, n number of months away from a start date.

To use the EDATE Excel Worksheet Function, Select cell and Type:

edate formula syntax

(Notice how the formula input appear)

EDATE Function syntax and Inputs:

start_date – The start date in Excel serial number format or entered as a date with quotations (“s) surround the date. Example: You can not enter 11/12/2015 directly into the cell. Instead you need to enter “11/12/2015” or you would need to use the corresponding serial number: 42320. Alternatively, you can reference a cell with the date 11/12/2015 entered. Excel automatically converts dates stored in cells into serial format (unless the date is entered as text).

months – Number of months away from start date. Number can be positive (future) or negative (past).

EDATE Examples

Add Months to a Date

The EDATE Function is used to add months to a date:

Add One Month

ADD one monthAdd Six Months

ADD Six monthsSubtract Months From a Date

The EDATE Function can also be used to subtract months from a date.

Subtract One Month

Subtract One MonthSubtract Six Months

Subtract Six MonthsWhat About End of the Month?

If the day of the start date is greater than the number of days in the end month, the last day of the month will be displayed:

ADD one month to January

The reverse is not true.

 

Subtract one Months for FEB

To ensure you get the last day of the month, you can use the EOMONTH Function instead:

Edate EOMONTHEDATE in Google Sheets

The EDATE Function works exactly the same in Google Sheets as in Excel:

EDATE Google Sheet

EDATE Examples in VBA

vba code generator
You can also use the EDATE function in VBA. Type:
Application.Worksheetfunction.Edate(start_date,months)
For the function arguments (start_date, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel