Subtract Date in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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.

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: First, calculate 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 to see the number of days between these two days.
Subtract Date in Excel Example 1.2

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.

 Example 1.3
  • Select the B2 cell as the reference.
Subtract Date in Excel Example 1.4
  • 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.
 Example 1.5
  • Now, select the A2 cell as the reference and close the bracket to get the result regarding 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 the result as 1 year, but the actual year difference is 1.28 years.
Subtract Date in Excel Example 1.8
  • We have zero in cells D5 and D7 because both dates reside in the same year.
Subtract Date in Excel Example 1.17
  • 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.
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

We have resulted in years not with rounded years but with actual years difference.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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

Subtract Date in Excel Example 1.16

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.

Subtract Date in Excel (D option)

Now, look at the “M” parameter. Between the 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 ignores the month and year and takes only days; between 18 and 28, there are 10 days.

Now, look at the “YM” parameter.

 Example 2.4

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.

Subtract Date in Excel Example 2.5

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.

This article is a guide to Subtract Dates in Excel. Here, we discuss subtracting date using two methods (Direct Subtraction, using the DATEDIF function) and a downloadable Excel template. You may learn more about Excel from the following articles: –