WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Subtract Date in Excel

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

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

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.

  • To get a year difference, we need to use the YEAR function in excel, so open the function.

 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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

  • How to Compare Two Dates in Excel?
  • What is SUMIFS With Multiple Criteria?
  • SUMIFS with Dates in Excel
  • Add Months to Date in Excel
10 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Subtract Date Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More