Subtract months from date

To subtract months from a date you can apply the Excel EDATE or DATE functions

Example: Subtract months from date

Subtract months from date

METHOD 1. Subtract months from date using the Excel EDATE function

EXCEL

=EDATE(B5,-C5)
This formula subtracts five (5) months from the date specified in cell (B5) using the Excel EDATE function. This formula links to specific cells in the worksheet for the formula parameters, however, you can also enter the number of months you want to subtract and the date that you want to subtract the months from directly into the formula (e.g. =EDATE("15/03/2017",-5)).

METHOD 2. Subtract months from date using the Excel DATE function

EXCEL

=DATE(YEAR(B5),MONTH(B5)-C5,DAY(B5))
This formula subtracts five (5) months from the date specified in cell (B5) using the Excel DATE function. This formula links to specific cells in the worksheet for the formula parameters, however, you can also enter the number of months you want to subtract and the date that you want to subtract the months from directly into the formula (e.g. =DATE(YEAR("15/03/2017"),MONTH("15/03/2017")-5,DAY("15/03/2017"))).

METHOD 1. Subtract months from date

VBA

Sub Subtract_months_from_date()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
Set smonths = ws.Range("C5")
Set sdate = ws.Range("B5")
'subtract the specified number of months from the date
ws.Range("F4") = DateAdd("m", -smonths, sdate)

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 Analysis.
Months to subtract: This example references to cell ("C5") to source the number of months to subtract from the date. Therefore, if using the same VBA code cell ("C5"), in the Analysis worksheet, must be populated with the value that represents the number of months you want to subtract from the date. You can also enter the number of months you want to subtract directly from the VBA code by replacing the cell reference (ws.Range("C5")) with the number of months.
Date: This example references to cell ("B5") to source the date that you want to subtract the months from. Therefore, if using the same VBA code cell ("B5"), in the Analysis worksheet, must be populated with the date that you want to subtract the months from. You can also enter the date directly into the VBA code by replacing the cell reference (ws.Range("B5")) with the date inside the double quotation marks (e.g. "15/03/2017").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Months to subtract: Select the number of months that you want to subtract from the date by changing the value in cell ("B5") in the Analysis worksheet.
Date: Select the date that you want to subtract the months from by changing the date in cell ("B5") in the Analysis worksheet.

Explanation about the formula used to subtract months from date

EXPLANATION

EXPLANATION
To subtract months from a date you can apply the Excel EDATE or DATE functions. In this tutorial we explain how this can be achieved by using Excel and VBA.
FORMULAS
=EDATE(date,-number_of_months)
=DATE(YEAR(date),MONTH(date)-number_of_months,DAY(date))

ARGUMENTS
date: The date that you want to subtract the months from.
number_of_months: Number of months to subtract from the date.