Adding and Subtracting Days, Months, and Years from Dates in Microsoft Excel
- Acquired Office Skills
- Aug 7, 2021
- 2 min read
Let's say you'd like to create a schedule for an event occurring 36 days from now. You can easily perform this calculation with a few simple steps in Microsoft Excel.
Adding Days to Dates in Microsoft Excel
Formula: Initial Date + Number of Days written as a positive number
Subtracting Days from Dates in Microsoft Excel
Formula 1: Initial Date - Number of Days written as a positive number
Formula 2: Initial Date + Number of Days written as a negative number
Add or Subtract Months from Dates
The EDATE function is the most efficient way of adding/subtracting months from a date. The syntax of the EDATE function is =EDATE(start_date,months)
Start Date (Required): The initial date to be used for calculations
Months (Required): The number of months to be added/subtracted from the start date.
Note: To perform an addition you are required to use a positive number to represent the number of months, however when subtracting months a negative number should be used to represent the number of months.
Add or Subtract Years from Dates
A combination of the DATE, YEAR, MONTH, and DAY functions is useful when adding/subtracting years from a date. It is represented by the syntax:
=DATE(YEAR(START DATE)+YEARS TO BE ADDED,MONTH(START DATE),DAY(START DATE))
Explanation
DATE function: Converts the outputs of the other three functions to a date
YEAR function: Pulls the Year value from the initial cell
+ YEARS TO BE ADDED: Adds the number of years to the value outputted from the year function. To subtract use a negative number to represent the value to be subtracted.
MONTH and DAY functions were used to return the actual month and day of the Start Date
Add or Subtract Days, Months and Years from Dates at once
The formula for performing this action is quite similar to the one above. The only difference is that in this case figures will be added to the months and days of the start date. This is represented by the syntax:
=DATE(YEAR(START DATE)+YEARS TO BE ADDED,MONTH(START DATE)+MONTHS TO BE ADDED,DAY(START DATE)+DAYS TO BE ADDED)
Note: Whilst the addition/subtraction of years from a date can also be done using the EDATE function by converting the number of years to the month, this method is not always the most efficient especially when working with a large amount of data.
Comments