EDATE Function: The easiest way to add or subtract months to your dates!
We deal with a lot of Date variables in Excel and to support that, Excel has a vast variety of formulas which work with Dates. When it comes to adding or subtracting certain months from any given date, the Excel formula to use is EDATE.
The syntax for EDATE function is:
=EDATE(start_date,months)
- start_date – It is the date to which you want to add months
- months – it is the number of months you would like to add or subtract to the start_date. Use a positive integer to add months and a negative integer to subtract months
Example 1: Adding months to any given Date
Now that we know the syntax of EDATE formula. Let look at an example to understand how to use this formula to add months to a date
- Create a table as shown below. Let’s say this represents the various dates to which we need to add the number of months mentioned in the Months column
- Now to add the Months to the Date, we write the formula =EDATE(A2,B2) in cell C2 as shown below
- You’ll notice that the result of the above formula will be a serial number. Drag the formula till C11 to get the results as shown below
- You can change the number format of the cells to Date to represent the date in the correct format
- When formatted in the dd-mmm-yy format, the results are as shown below
- This is how we can add months to any date using the EDATE formula.
Example 2: Subtracting Months from a given date
Since we saw an example where we added months to any given date, now we’ll look at an example where we subtract months from any date
- Create a table as shown below. Let’s say this represents the various dates from which we need to subtract the number of months mentioned in the Months column
- No to add the Months to the Date, we write the formula =EDATE(A2,B2) in cell C2 as shown below
- You’ll notice that the result of the above formula will be a serial number. Drag the formula till C11 to get the results as shown below
- You can change the number format of the cells to Date to represent the date in the correct format
- When formatted in the dd-mmm-yy format, the results are as shown below
- This is how we can subtract months to any date using the EDATE formula.
You can download and refer to the solution file from the link below:
You can also check my Excel Date and Time Masterclass Video Tutorial below, which explains Date and Time in Excel from Scratch!
Please share
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018