Subtract Date in Excel

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 number of days or it could be all. Now let’s see how to subtract one date from another. We can subtract dates in excel using two methods i.e. “Direct Subtraction” and Using “DATEDIF Function“.

Adding or Subtracting one date with another is the common task we do but this isn’t the easy operations, 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 gives only the number of days between two dates.

For example, look at the below data in excel worksheet.

• Step 1: Now first calculating the difference between two dates in excel, so apply the B2 – A2 formula.

• Step2: We may get the result in terms of date only but do not panic because we need to apply number formatting for this to see the number of days between these two days.

Here we have got a number of days as a result.

Similarly, we can get a number of years as a result. 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.

• This 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 A2 cell as the reference and close the bracket to get the result in terms of 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 got the result as 1 year but the actual year difference is 1.28 years.

• In cells D5 & D7 we have zero as the result because both the dates are residing in the same year.

• So, in such cases, we need to use different function i.e. “YEARFRAC” function. This function calculating the difference between two dates in excel. Ok, let’s 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 [basis] is optional so leave out this. we get the following result.

• Apply the formula to other cells to get the result.

There you go we have resulted in terms of years not with rounded years but with actual year’s difference.

#2 Using DATEDIF Function

Using DATEDIF function we can do a variety of calculations. If you have already tried searching the “DATEDIF” function hold on for a moment.

Oh no! There is no DATEDIF function in my excel.

DATEDIF is a hidden function, so we will not get any matching results of functions when we actually start applying the formula.

Start Date & 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” this gives the difference between two dates as “Months”.
• “Y” this gives the difference between two dates as “Years”.
• “MD” this 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 two dates there are 15 months.

Now, look at “Y”.

Now, look at the “MD” parameter.

This one ignored both the month and year and takes only days and between 18 & 28 there are 10 days.

Now, look at the “YM” parameter.

This one ignored 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 ignored Years and gives the days difference between 18^th July to 28^th Oct as 102 days.

Like this, we can subtract dates in excel.

Things to Remember About Subtract Date in Excel

• DATEDIF is a hidden formula in excel.
• While using direct subtraction we need to select the latest date first then we need to select the old date, otherwise, we will get the result in minus.

Recommended Articles

This has been a guide to Subtract dates in excel. Here we discuss how to subtract date using two methods (Direct Subtraction, Using DATEDIF Function) along with a downloadable excel template. You may learn more about excel from the following articles –

• How to Compare Two Dates in Excel?
• What is SUMIFS With Multiple Criteria?
• SUMIFS with Dates in Excel
• Add Months to Date in Excel