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.
Table of contents
#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 excelYEAR Function In ExcelThe year function in excel is a date function to calculate the year from a given date. This function takes a serial number as an argument and returns a four-digit numeric value representing the year of the given date, formula = year (serial number), 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 [basis] 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.
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: –
- How to Compare Two Dates in Excel?How To Compare Two Dates In Excel?Comparing two dates in Excel can be useful in a variety of situations, such as determining whether one date is equal to the other, or determining whether one date is greater than or less than another, and so on.
- What is SUMIFS With Multiple Criteria?
- SUMIFS with Dates in ExcelSUMIFS With Dates In ExcelSUMIFS function is used when there is more than one criterion. When it is fulfilled, the range of cells are summed. It also supports dates as the criteria and the operators for criterion. Syntax =SUMIFS( sum range, range for date, criteria date, date 2, criteria date 2).
- Add Months to Date in ExcelAdd Months To Date In ExcelIn Excel, the EDATE function adds the specified number of months to the supplied date and returns the next specified month for the same day. The EDATE function has the syntax =EDATE(start date, months).