Excel EDATE Function

The Excel EDATE function returns a date after the addition or subtraction of a specified number of months from a selected date

Example: Excel EDATE Function

Excel EDATE Function

METHOD 1. Excel EDATE Function using hardcoded values

EXCEL

=EDATE("15/03/2017",5)
Result in cell D5 (15/08/2017) - returns the revised date after adding five months onto the date specified in the formula.

=EDATE("15/03/2017",-5)
Result in cell D6 (15/10/2016) - returns the revised date after subtracting five months onto the date specified in the formula.

METHOD 2. Excel EDATE Function using links

EXCEL

=EDATE(B5,C5)
Result in cell D5 (15/08/2017) - returns the revised date after adding five months, represented in cell (C5) onto the date specified in cell (B5).

=EDATE(B6,C6)
Result in cell D6 (15/10/2016) - returns the revised date after subtracting five months, represented in cell (C6) onto the date specified in cell (B6).

METHOD 3. Excel EDATE function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Date & Time > EDATE > populate the input boxes

=EDATE("15/03/2017",5)
Note: in this example we are populating all the input boxes associated with the EDATE function including start date and number of months.
Built-in Excel EDATE Function using hardocded values

METHOD 4. Excel EDATE function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Date & Time > EDATE > populate the input boxes

=EDATE(B5,C5)
Note: in this example we are populating all the input boxes associated with the EDATE function by referencing to the cells that contain the start date (cell (B5)) and number of montsh (cell (C5)).
Built-in Excel EDATE Function using links

METHOD 1. Excel EDATE function using VBA with hardcoded values

VBA

Sub Excel_EDATE_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("EDATE")
'apply the Excel EDATE function
ws.Range("D5") = WorksheetFunction.EDate("03/15/2017", 5)
ws.Range("D6") = WorksheetFunction.EDate("03/15/2017", -5)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named EDATE.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

METHOD 2. Excel EDATE function using VBA with links

VBA

Sub Excel_EDATE_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("EDATE")
'apply the Excel EDATE function
ws.Range("D5") = WorksheetFunction.EDate(ws.Range("B5"), ws.Range("C5"))
ws.Range("D6") = WorksheetFunction.EDate(ws.Range("B6"), ws.Range("C6"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named EDATE.
Date: If using the exact same VBA code you will need to capture the starting dates in cells ("B5") and ("B6").
Months: If using the exact same VBA code you will need to capture the the number of months that you want to add or subtract in cells ("C5") and ("C6").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Start Date: Select the start dates by changing the cell references ("B5") and ("B6") to any cell in the worksheet that contains the start date and doesn't conflict with the formula.
Months: Select the number of months you want to add or subtract by changing the cell references ("C5") and ("C6") to any cell in the worksheet that represents the number of months you want to add or subtract and doesn't conflict with the formula.

Usage of the Excel EDATE function and formula syntax

EXPLANATION

DESCRIPTION
The Excel EDATE function returns a date after the addition or subtraction of a specified number of months from a selected date.
SYNTAX
=EDATE(start_date,months)

ARGUMENTS
start_date: (Required) The date from which you want to add or subtract months.
months: (Required) The number of months that you want to add or subtract from the start date.