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 a common task we do, but this isn’t an easy operation, so in this article, we will show you how to subtract dates in excel using various methods.

Excel-Subtract-Date

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Subtract Date in Excel (wallstreetmojo.com)

You can download this Subtract Date Excel Template here – Subtract Date Excel Template

#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.

Subtract Date in Excel Example 1
  • Step 1: Now, first calculating the difference between two dates in excel, so apply the B2 – A2 formula.
 Example 1.1
  • Step 2: 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.
Subtract Date in Excel Example 1.2

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.

 Example 1.3
  • Select the B2 cell as the reference.
Subtract Date in Excel Example 1.4
  • 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.
 Example 1.5
  • Now select the A2 cell as the reference and close the bracket to get the result in terms of the number of years.
Subtract Date in Excel Example 1.6
  • We get the following result.
 Example 1.7.0
  • 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.
Subtract Date in Excel Example 1.8
  • In cells D5 & D7, we have zero as the result because both the dates are residing in the same year.
Subtract Date in Excel Example 1.17
  • So, in such cases, we need to use different functions, i.e., the “YEARFRAC” function. This function calculating the difference between two dates in excel. Ok, let’s open this function now.
Example 1.9
  • Select the start date as the lowest date, so, in this case, first, we need to select the A2 cell.
Subtract Date in Excel Example 1.10
  • Now select the end date as a B2 cell reference.
Example 1.11
  • The last parameter [basis] is optional so leave out this. we get the following result.
Subtract Date in Excel Example 1.12
  • Apply the formula to other cells to get the result.
 Example 1.14

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 the DATEDIF function, we can do a variety of calculations. If you have already tried searching the “DATEDIF” function, hold on for a moment.

Subtract Date in Excel Example 1.16

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.

DATEDIF (Start Date, End Date, Difference Type)

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” gives the difference between two dates as “Months.”
  • “Y” this 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.

Example 2

Now, look at the “M” parameter. Between two dates, there are 15 months.

Subtract Date in Excel Example 2.1

Now, look at “Y.”

 Example 2.2

Now, look at the “MD” parameter.

Subtract Date in Excel Example 2.3

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.

 Example 2.4

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.

Subtract Date in Excel Example 2.5

This one ignored 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

  • 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>