Table Of Contents
How to Subtract Date in Excel?
To subtract dates, we need at least two dates. Common scenarios of subtracting dates are to find the number of years, the number of months or days, or it could be all. Now, let us see how to subtract one date from another. We can subtract dates in Excel using direct subtraction and the "DATEDIF Function".
Adding or subtracting one date from another is a common task, but this is not an easy operation. So in this article, we will show you how to subtract dates in Excel using various methods.
#1 Direct Subtraction
Direct subtraction is simply deducting one date from another. It only gives the number of days between two dates.
For example, look at the below data in an Excel worksheet.
- Step 1: First, calculate the difference between two dates in Excel, so apply the B2 – A2 formula.
- Step 2: We may get the result in terms of date only but do not panic because we need to apply number formatting to see the number of days between these two days.
Here, we have got the number of days as a result.
Similarly, we can get the number of years as a result. But, first, we will see how to get the year difference.
- To get a year difference, we need to use the YEAR function in excel, so open the function.
- Select the B2 cell as the reference.
- It will extract the year portion from the selected cell B2 since we are subtracting to apply minus sign and open one more YEAR function.
- Now, select the A2 cell as the reference and close the bracket to get the result regarding the number of years.
- We get the following result.
- Now, there exists a problem because when we look at the first result, cell D2. We have the result as 1 year, but the actual year difference is 1.28 years.
- We have zero in cells D5 and D7 because both dates reside in the same year.
- So, in such cases, we need to use different functions, the “YEARFRAC” function. This function calculates the difference between two dates in Excel. Let us open this function now.
- Select the start date as the lowest date. So, in this case, first, we need to select the A2 cell.
- Now select the end date as a B2 cell reference.
- The last parameter is optional so leave out this. We get the following result.
- Apply the formula to other cells to get the result.
We have resulted in years not with rounded years but with actual years difference.
#2 Using DATEDIF Function
Using the DATEDIF function, we can do a variety of calculations. Hold on if you have already tried searching the “DATEDIF” function.
There is no DATEDIF function in my Excel.
The DATEDIF function is hidden, so we will not get any matching results of functions when we start applying the formula.
DATEDIF (Start Date, End Date, Difference Type)
The Start Date and End Date are common. But one thing we need to mention is what kind of difference we need between selected dates. Below are the parameters and their results.’
- “D” gives the difference between two dates as “Days.”
- “M” gives the difference between two dates as “Months.”
- “Y” gives the difference between two dates as “Years.”
- “MD” gives the difference between two dates as “Days” by ignoring MONTHS & YEARS.
- “YM” gives the difference between two dates as “Months” by ignoring YEARS.
- “YD” gives the difference between two dates as “days,” ignoring YEAR.
First, we will see the “D” option. Take below two dates. The difference in days is 467.
Now, look at the “M” parameter. Between the two dates, there are 15 months.
Now, look at “Y.”
Now, look at the “MD” parameter.
This one ignores the month and year and takes only days; between 18 and 28, there are 10 days.
Now, look at the “YM” parameter.
This one ignores years and gives the month between two dates as 3 because from “Jul” to “Oct,” there are only three months.
Now, look at the “YD” parameter.
This one ignores years and gives the days difference between 18th July to 28th Oct as 102 days.
Like this, we can subtract dates in excel.
Things to Remember About Subtract Date in Excel
- The DATEDIF is a hidden formula in Excel.
- While using direct subtraction, we must select the latest date first. Then, we must choose the old date. Otherwise, we will get the result minus.