Excel’s subtraction is not limited to numbers. You can subtract dates and extract the duration between the starting date and the ending date. I personally subtract dates in Excel to quickly find out someone’s age, anniversaries dates, etc.
However, if you are using Excel for professional purposes, you can calculate the total workdays or due dates using in-built functions. Depending on the type of information your need, we have compiled 6 different functions to subtract dates in Excel.
Using DAYS Function
Excel’s DAYS function returns the duration of days between two dates. Use this function if you want to find out the number of days between the end date and the start date.
Syntax: DAYS(end_date, start_date)
For Example: Start Date: 9/5/2022 and End Date: 10/10/2023.
To subtract days, enter =DAYS(“10/10/2023”,“9/5/2022”). You will get 400 days as result.
Alternatively, you can also add a cell reference to the formula. This will allow you to use flash-fill for other ranges too. For this, type in =DAYS(B2,A2) formula and enter. Here, you do not have to put the cell references inside a quotation mark.
Note: You will get a #NUM error if the beginning date is larger than the ending date.Using DATEDIF Function
With the DAYS function, you will get the differences in the number of days only. So, if you have to subtract years, months, weeks, and days of two given dates, use the DATEDIF Function.
Syntax: DATEDIF(start_date,end_date, unit)
The function arguments of this syntax are as follows:
- Start_date: Beginning date
- End_date: Last date
- Unit: Time Unit you want as output. For example, years, months, days, etc.
- Y: Number of Years
- M: Number of Months
- D: Total Days
- MD: Difference between the days of given dates. It ignores months and years during calculation.
- YM: Difference between the months of dates. It ignores days and years.
- YD: Difference between days. Ignores years.
Suppose, you have Start Date: 9/5/2022 in the A2 cell and End Date:10/10/2023 in the B2 cell as shown in the given picture.
Now, let’s see the subtraction formula for these dates below.
| Subtraction | Formula | Result | Description |
| Subtract Days | =DATEDIF(A2,B2,“d”) | 400 days | In the given formula, A2 is start_date and B2 is end_date. Since we want output in days, the unit is “d”. This formula calculates the total days between “9/5/2022” and “10/10/2023“. Then, returns 400 days as result. |
| Subtract Weeks | =DATEDIF(A2,B2,“d”)/7 | 57.14 weeks | Like the above formula, A2 is start_date and B2 is end_date. To return the days in weeks, we will divide “d” days by 7. Firstly, this formula computes 400 days as result from “9/5/2022” to “10/10/2023“. When 400 days are divided by 7, you’ll get 57.14 weeks. |
| Subtract Months | =DATEDIF(A2,B2,“m”) | 13 months | Here, start_date is A2 and end_date is B2. To get the differences in months, we are referring to the “m” unit in the formula. This formula returns 13 months difference between “9/5/2022” and “10/10/2023“. |
| Subtract Years | =DATEDIF(A2,B2,“y”) | 1-year | Start_Date is A2 and End_Date is B2. To subtract “9/5/2022” and “10/10/2023” in years, “y” is the unit. It returns 1 year as the outcome. |
Note: If you get the output in a date format, convert it to a General format from the Home Tab.Using EDATE Function
DATEDIF Function is limited to calculating two given dates.With the EDATE Function, however, you can subtract months from a specific date. This function is best for users who want to know anniversary dates, retirement dates, maturity dates, etc.
EDATE Function returns a new date as a serial number which Excel reads as a date. You can change it to the date format from the Number section of the Home Tab.
Syntax: EDATE(start_date, months)
The function arguments are:
- Start_date: Beginning Date
- Months: Total months before or after the start date
Suppose, you want to subtract the months of Column B from the dates of Column A. For this, enter the formula=EDATE(A2, -B2). Here, A2 is the start_date and -B2 is the number of months to subtract from.
Using the DATE Function
Using the EDATE Function, you can subtract months from a date. But, what if you had to subtract years, months, and days from the given date?
During such cases, you can use Excel’s DATE function. Let’s assume, you have to subtract the date in the given data. For this, enter =DATE(YEAR(A3)-B3,MONTH(A3)-C3,DAY(A3)-D3) in E3 cell.
In this formula, we’ve nested the DATE function with YEAR, MONTH, and DAY functions as arguments. So, each individual function will subtract the A3 cell containing a date. For instance, A3-B3, A3-C3, and so on.
Using NETWORKDAYS Function
If you have to subtract dates to find out the total workdays between the beginning and end date, use NETWORKDAYS Function. As the function excludes weekends and holidays, it can come in handy to evaluate employee benefits depending on the complete working days.
Syntax: NETWORKDAYS(start_date, end_date, [holidays])
Function Arguments:
- Start_date: Beginning Date
- End_date: Last Date
- Holidays: Dates to exclude as holidays. For Instance, State holidays (optional)
Suppose, you have to calculate workdays from the given data.
| Subtraction | Formula | Result | Description |
| Subtract Dates | =NETWORKDAYS(A2,B2) | 287 days | In the formula, A2 is the start_date and B2 is the end_date. Here, we haven’t added any holidays. So, this function will return the total working days from “9/5/2022” to “10/10/2023” without the weekends. i.e. Saturday and Sunday. |
| Subtract Dates with a holiday | =NETWORKDAYS(A2,B2,C2) | 286 days | Here, the formula returns the complete workdays between “9/5/2022” and “10/10/2023” with “10/20/2022” as a non-working day. |
| Subtract Dates with multiple holidays | =NETWORKDAYS(A2,B2,C2:C5) | 283 days | In the formula, A2 represents the start_date and B2 refers end_date. C2:C5 are the days with holidays. This formula calculates the total workdays from “9/5/2022” to “10/10/2023” including 4 holidays. |
Using TODAY Function
TODAY Function returns the present date in months, days, and years. For example, if you enter =TODAY() formula, you’d get the current date. i.e. 2/21/2023 at the time of writing this article.
This function does not take any arguments. However, you can use this function to calculate a person’s age.
Suppose, you have to find out the age of the person who is born in the year 2005. For this, we will use the =YEAR(TODAY())-2005 formula.
In this formula, we have nested the YEAR function with the TODAY Function. So, when the year 2005 is subtracted from the current year (2023), you’ll get 18 as a person’s age.