How To Subtract Dates in Excel

Last updated: August 23, 2026

Do you need to quickly subtract dates in Excel?

Subtracting dates in Excel can help you calculate the days, months, or years between two dates. It can also determine workdays and track project timelines. The good news? Excel makes it surprisingly simple, with built-in formulas and tools that do the heavy lifting for you.

In this post, we’ll break down step-by-step how to subtract dates in Excel like a pro, including how to minus days from a date, how to deduct one date from another to get years, and what to do when the answer comes back as an error or as the wrong kind of value.

What this guide covers

1. Can you subtract dates in Excel?
2. How do you subtract two dates to get days?
3. How do you use DATEDIF to get months and years?
4. How do you subtract dates in Excel to get years?
5. How do you subtract days from a date?
6. How do you subtract months or years from a date?
7. How do you count only workdays between two dates?
8. How do you subtract date and time values?
9. How do you calculate days since a date?
10. Which date subtraction method should you use?
11. Why is your date subtraction not working?
12. FAQ

Can you subtract dates in Excel?

Yes. Excel stores every date as a serial number counting forward from 1 January 1900, so a date is just a number wearing a date format. That is why plain arithmetic works on dates the same way it works on prices or quantities. You do not need a special function to minus one date from another, and there is no add-in to install.

The rule: if two cells both hold real dates, =B2-A2 gives you the whole number of days between them. Everything else in this guide is a variation on that one idea.

How do you subtract two dates to get days?

Before we begin, you should ensure your dates are formatted correctly as dates (date format not text). Incorrect formatting can lead to Excel errors. You can easily check and change cell formats in the Format Cells dialog box.

Now, the simplest way to subtract dates is by using a basic formula.

Steps:

1. Enter the start date in one cell (e.g., A1).

2. Enter the end date in another cell (e.g., B1).

3. Use the formula =B1-A1 to calculate the difference in days. This same duration calculation feeds the bar lengths in a gantt chart template excel.

   

Including start and end dates:

To include both dates, use =B1-A1+1. This is the difference between a duration and a count. A task running from Monday to Friday lasts 4 days of elapsed time but occupies 5 calendar days, and the +1 is what turns one into the other.

If the answer looks like a date rather than a number, nothing is wrong with your formula. The result cell has simply picked up date formatting from the cells around it, so Excel is showing serial number 5 as 5 January 1900. Select the cell and set Home > Number Format to General or Number, and the count appears.

Read more: How to Use the EDATE function in Excel.

How do you use DATEDIF to get months and years?

The DATEDIF function is more advanced than the basic formula in the previous example. This date function offers more flexibility for calculating differences in days, months, or years.

Syntax:

=DATEDIF(Start_Date, End_Date, Unit)

The third argument is the unit, in quotation marks, and it is where all the power sits. The table below uses a start date of 10 January 2023 and an end date of 15 March 2025 so you can see what each unit actually returns for the same pair of dates.

Example:

Note: DATEDIF is not listed in Excel’s function library but works perfectly when typed manually. Excel will not autocomplete it and will not prompt you for its arguments, so type the whole thing yourself.

The "MD" warning you should know about

Microsoft's own DATEDIF documentation carries an explicit caution: the "MD" argument may result in a negative number, a zero, or an inaccurate result. DATEDIF exists mainly to support workbooks carried over from Lotus 1-2-3, and "MD" is the unit where that shows. If you need the leftover days in a years plus months plus days readout, this is safer:

=B2-EDATE(A2,DATEDIF(A2,B2,"m"))

That jumps forward by the number of whole months and then counts the remaining days directly, which sidesteps the bug entirely.

How do you subtract dates in Excel to get years?

This is the most common follow-up to a plain date subtraction, and the answer depends on whether you want whole years or a decimal.

Whole years, the age calculation

=DATEDIF(A2,B2,"y") returns complete years only. Someone born on 1 June 2000 measured to 31 May 2025 returns 24, not 25, because the birthday has not landed yet. That is usually exactly what you want. For a live age that updates itself, swap the end date for TODAY():

=DATEDIF(A2,TODAY(),"y")

Decimal years

If you want 2.6 years rather than 2 years, use YEARFRAC, which handles leap years properly instead of guessing with 365:

=YEARFRAC(A2,B2)

Dividing by 365 works as a rough approximation but drifts by roughly a quarter of a day per year, which becomes visible over long spans. YEARFRAC does not drift.

Years and months together

For a full "2 years, 2 months, 5 days" readout you are combining three DATEDIF calls, and that belongs with the month-unit calculations. We cover the whole family, including partial and calendar months, in how to calculate months between dates in Excel.

How do you subtract days from a date?

Everything above subtracts one date from another date. This is the opposite job: you have one date and you want to move it backwards by a number of days. Excel treats that as ordinary arithmetic too.

=A2-30 returns the date 30 days before whatever is in A2. If A2 holds 15 March 2025, the result is 13 February 2025.

If the number of days lives in its own cell, point at the cell instead so you can change it without editing the formula:

=A2-B2, where B2 holds a plain number such as 30.

The formatting gotcha runs in reverse here

When you subtract a number from a date, the answer is a date. If it appears as something like 45701, the cell is formatted as a number. Select it and set Home > Number Format to Short Date. This is the mirror image of the problem in the direct subtraction section, and it catches people out because the fix is the opposite one.

Subtracting working days rather than calendar days

To go back 10 working days rather than 10 calendar days, use WORKDAY with a negative number:

=WORKDAY(A2,-10)

Add a holiday range as a third argument to skip those too, as in =WORKDAY(A2,-10,$E$2:$E$12).

How do you subtract months or years from a date?

Subtracting a fixed number of days will not reliably move you back a month, because months are not all the same length. Use EDATE instead.

• =EDATE(A2,-1) returns the same day one month earlier.
• =EDATE(A2,-3) goes back a quarter.
• =EDATE(A2,-12) goes back a year.

Why EDATE and not DATE: EDATE knows that months have different lengths. Feed it 31 March and ask for one month earlier and it returns 28 February, because 31 February does not exist. The alternative, =DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)), silently rolls forward into the next month in that situation and hands you 3 March. EDATE is the safer default whenever the day-of-month is above 28.

The same caution applies to years. =EDATE(A2,-12) handles a 29 February start date correctly, where =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)) rolls it to 1 March.

How do you count only workdays between two dates?

If you need to exclude weekends and holiday dates, the NETWORKDAYS function should be your go-to solution.

Syntax:

=NETWORKDAYS(Start_Date, End_Date, [Holidays])

• Start_Date: The earlier date.

• End_Date: The ending date.

• [Holidays]: An optional range of dates to exclude (e.g., public holidays).

Example:

Note: The formula calculates the number of workdays (Monday through Friday) between the start date (01/01/2025) and the end date (01/15/2025), excluding weekends and any holidays specified in the range C1:C1.

Two things NETWORKDAYS does that plain subtraction does not. It counts both endpoints, so a Monday to Friday range returns 5 rather than 4. And it never returns a negative number of workdays by accident, because if you pass the dates in the wrong order it simply returns a negative count rather than an error.

If your weekend is not Saturday and Sunday

NETWORKDAYS hard-codes a Saturday and Sunday weekend. For any other pattern, use NETWORKDAYS.INTL, which takes a weekend code as a third argument:

=NETWORKDAYS.INTL(A2,B2,7) treats Friday and Saturday as the weekend. Code 11 means Sunday only, and you can also pass a seven-character string of 0s and 1s for a fully custom pattern.

Version note: NETWORKDAYS.INTL and WORKDAY.INTL were introduced in Excel 2010. In Excel 2007 or earlier they return #NAME?, and you are limited to the plain NETWORKDAYS and WORKDAY versions.

How do you subtract date and time values?

When your cells carry a timestamp rather than a bare date, subtraction still works, but the answer arrives as a fraction of a day and needs the right number format to be readable.

Calculating elapsed time.

To calculate differences in hours, minutes, or seconds, include time in your date values and adjust the formula accordingly. Here’s how you can achieve this:

1. Input date and time: Ensure your cells contain both date and time (e.g., 01/01/2025 10:00 AM).

2. Use direct subtraction: Subtract one cell from another (e.g., =B2-A2).

   

3. Format Result: Format the resulting cell as "Custom" and set it to hh:mm:ss for hours, minutes, and seconds.

   

Use square brackets if the gap exceeds 24 hours. A custom format of hh:mm:ss resets at midnight, so 30 hours displays as 06:00:00. Change the format to [h]:mm:ss and the same result displays as 30:00:00. The square brackets tell Excel not to roll over.

To get a whole number of hours instead of a time, multiply by 24: =(B2-A2)*24 returns 30 rather than a formatted duration. Multiply by 1440 for minutes.

This approach is great for tracking task durations or measuring time intervals. Computing days until a deadline is the bread-and-butter of a project task tracker, where each task row shows time remaining at a glance.

If the time component is getting in your way and you only care about whole days, strip it first. How to remove time from a date in Excel covers INT, TEXT and Find and Replace approaches. A date subtraction that returns 45.75 instead of a round number is almost always a hidden timestamp.

How do you calculate days since a date?

For dynamic calculations that update automatically, use the TODAY() function. This date function returns the current date without the time component.

1. Days since a specific date: Subtract a past date from TODAY() (e.g., =TODAY()-A1).

2. Days until a future date: Reverse the order, as in =A1-TODAY().

3. Dynamic updates: The formula updates daily to reflect the current date.

Example:

One caution about TODAY(). It is volatile, meaning it recalculates every time the workbook opens or anything changes. That is the point when you want a live counter, but it also means a saved snapshot will not hold its value. If you need the number frozen, copy the cell and use Paste Special > Values.

Read more: How to convert numbers to date in Excel.

Which date subtraction method should you use?

Why is your date subtraction not working?

Almost every date subtraction problem comes down to one of two things: the cell is not holding what you think it is holding, or the answer is formatted as the wrong type. Here is how to tell which.

Final Thoughts on "How To Subtract Dates in Excel"

Date subtraction in Excel is necessary for tasks such as tracking your project's scheduled date, calculating workdays, analyzing timelines, and running age or tenure reports. With tools like DATEDIF, NETWORKDAYS, EDATE and simple subtraction formulas, you can handle even complex date calculations confidently. Trip durations on an expense report template use this same date subtraction to split per-diem totals correctly.

If you take one thing away, make it this: a date is a number, so if a date formula is misbehaving, check the number format before you check the formula.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ on "How To Subtract Dates in Excel"

Can you subtract dates in Excel?

Yes. Excel stores dates as serial numbers, so =B2-A2 returns the number of days between two dates with no special function required. Make sure both cells hold real dates rather than text, and format the answer as General so it displays as a number rather than a date.

Why is my date subtraction formula showing a #VALUE! error?

This usually happens when your dates are formatted as text instead of actual dates. To fix this, select the cells, right-click, and choose Format Cells > Date. You can also use the DATEVALUE function to convert text into date values, or select the column and run Data > Text to Columns and click Finish.

Why does my date subtraction show a date instead of a number?

The result cell has picked up date formatting from the cells around it, so Excel is displaying the answer as a date rather than a count. Select the cell and set Home > Number Format to General or Number. The formula is fine, only the display was wrong.

Why does DATEDIF return a #NUM! error?

Because the start date is later than the end date. Microsoft documents this as expected DATEDIF behaviour, not a bug. Swap the two arguments, or make it order-proof with =DATEDIF(MIN(A2,B2),MAX(A2,B2),"d").

How do I subtract days from a date in Excel?

Subtract a plain number from the date, as in =A2-30 for the date 30 days earlier. The answer is a date, so format the cell as Short Date if it shows a serial number. To go back a number of working days instead, use =WORKDAY(A2,-10).

Can I subtract dates to calculate only workdays?

Yes. Use the NETWORKDAYS function to calculate the number of workdays between two dates. You can also add a list of holidays to exclude them from the calculation by specifying a range in the formula. If your weekend is not Saturday and Sunday, use NETWORKDAYS.INTL with a weekend code instead.

How do I calculate the time difference in hours, minutes, or seconds?

Include both date and time in your cells (e.g., 01/01/2025 10:00 AM). Subtract the start time from the end time and format the result as "Custom" with the format hh:mm:ss. If the gap is longer than 24 hours, use [h]:mm:ss instead so the hours do not reset at midnight.

Related Articles

How to Calculate Months Between Dates in Excel

How to Remove Time From a Date in Excel

How to Spell Check in Excel

How to Add Axis Labels in Excel

How to Find Square Root in Excel