How to subtract dates in Excel: A step-by-step guide

To subtract dates in Excel, type the later date in one cell, the earlier date in another, and enter a formula such as =B2-A2 to get the number of days between them.

This guide covers days, weeks, months, years, age, today's date, workdays and hours, plus the formulas that add or subtract time from a date and the fixes for #VALUE! and ##### results.

How to subtract dates in Excel: A step-by-step guide
DATEDIF with the y and ym units, plus a day count, turns 1/1/2014 to 5/6/2016 into 2 years, 4 months, 5 days. (Image: Microsoft)

Step by step tutorial: How to subtract dates in Excel

Excel stores every date as a serial number, so subtracting one date from another returns the count of days between them. January 1, 1900 is serial number 1 in the default date system.

  1. Type the start date in cell A2, for example 3/1/2026.
  2. Type the end date in cell B2, for example 3/31/2026.
  3. Click cell C2 and type =B2-A2.
  4. Press Enter. Excel shows 30, the number of days between the two dates.
  5. If the result looks like a date instead of a number, select C2, press Ctrl + 1, choose Number or General under Category, and select OK.
  6. Drag the fill handle at the corner of C2 down to repeat the formula for more rows.

Put the later date first in the formula. Subtracting a later date from an earlier one gives a negative number, which shows as ##### when the cell is formatted as a date.

Excel function to subtract dates

Plain subtraction answers most questions, but months, years and workdays need a function. Match the result you want to a row and copy the formula.

Result you want Formula (start in A2, end in B2) Why this one
Days between two dates =B2-A2 or =DAYS(B2,A2) Dates are serial numbers, so subtraction is exact
Weeks between two dates =(B2-A2)/7 Divides the day count by 7
Complete months =DATEDIF(A2,B2,"m") Counts full months only
Complete years or age =DATEDIF(A2,B2,"y") Counts full years only
Fraction of a year =YEARFRAC(A2,B2,1) Returns a decimal such as 0.58
Days from a date to today =TODAY()-A2 Updates every time the workbook recalculates
Workdays, no weekends =NETWORKDAYS(A2,B2) Skips Saturdays, Sundays and listed holidays
Hours between date-times =(B2-A2)*24 One day equals 24 hours
A date minus some days =A2-15 Returns the earlier date
A date minus some months =EDATE(A2,-3) Handles different month lengths

Add or subtract days to date with a formula

Subtracting a number from a date moves it back by that many days. Adding a negative number does the same, which helps when one column holds both positive and negative offsets.

  1. Enter your dates in column A, starting at A2.
  2. Enter the number of days to move each date in column B. Use a negative number, such as -15, to go back in time.
  3. Click cell C2 and type =A2+B2.
  4. Press Enter, then drag the fill handle down to copy the formula.
  5. To subtract a fixed number instead, type =A2-15 in C2 and press Enter.

If the result shows a five-digit number such as 43504, the cell is formatted as General. Press Ctrl + 1, select Date under Category, pick a format and select OK.

Subtract days from a date in Excel with =A2+B2 and negative day values
A value of -15 in column B moves 8-Feb back to 24-Jan, while 15 moves a date forward. (Image: Microsoft)

Add or subtract days to date without a formula

Paste Special can subtract a number from a whole block of dates in place. The original dates are overwritten, so work on a copy if you need the old values.

  1. Type the number of days to subtract, for example 7, in an empty cell.
  2. Select that cell and press Ctrl + C.
  3. Select the cells that hold the dates you want to change.
  4. Press Ctrl + Alt + V to open the Paste Special box.
  5. Under Paste, select Values so the date formatting stays in place.
  6. Under Operation, select Subtract, then select OK.
  7. Delete the helper cell that holds the number.

Select Add under Operation instead to move the dates forward.

Subtract days from dates without a formula using the Paste Special dialog
Select Values under Paste and Subtract under Operation, then select OK to take the copied number off every date. (Image: Microsoft)

Add or subtract weeks to date in Microsoft Excel

A week is seven days, so multiply the week count by 7 before you subtract it. The same idea works in reverse to count the weeks between two dates.

  1. Put the starting date in A2 and the number of weeks in B2.
  2. Click C2 and type =A2-B2*7 to go back that many weeks, or =A2+B2*7 to go forward.
  3. Press Enter. The result is the date the same weekday, that many weeks earlier or later.
  4. To count the weeks between a start date in A2 and an end date in B2, type =(B2-A2)/7 in another cell.
  5. Press Ctrl + 1, choose Number, set Decimal places to 2 and select OK so the partial week shows.

For whole weeks only, wrap the formula in INT: =INT((B2-A2)/7).

Add or subtract months to date in Microsoft Excel

Subtracting 30 days is not the same as subtracting a month, because months differ in length. EDATE moves a date by whole calendar months and takes a negative number to go back.

  1. Enter the starting dates in column A.
  2. Enter the number of months in column B, with a minus sign to subtract, such as -5.
  3. Click C2 and type =EDATE(A2,B2).
  4. Press Enter, then copy the formula down.
  5. If Excel shows a serial number, press Ctrl + 1, select Date and select OK.

Microsoft's own example: =EDATE("9/15/19",-5) returns 4/15/19.

Subtract months from a date in Excel with the EDATE function
A negative month count goes back in time, so 8-Mar with -1 in column B returns 8-Feb. (Image: Microsoft)

Add or subtract years to date in Microsoft Excel

The DATE function rebuilds a date from a year, month and day. Change only the year part to move a date by whole years.

  1. Put the starting date in A2 and the number of years in B2.
  2. Click C2 and type =DATE(YEAR(A2)-B2,MONTH(A2),DAY(A2)) to subtract the years.
  3. Press Enter. A date of 2/8/2019 minus 3 years returns 2/8/2016.
  4. To add years instead, change the minus sign to a plus: =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)).

YEAR pulls the year out of A2, the subtraction changes it, and MONTH and DAY pass the original month and day back to DATE.

Add or subtract a combination of years, months, weeks, and days to date in Microsoft Excel

One DATE formula can shift the year, month and day parts at once. Excel rolls any overflow forward, so 6 + 7 months becomes January of the next year.

  1. Put the starting date in A2.
  2. Enter the years in B2, months in C2, weeks in D2 and days in E2. Use negative numbers to subtract.
  3. Click F2 and type =DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2*7+E2).
  4. Press Enter. A start of 6/8/2019 with 1 year, 7 months and 15 days returns 1/23/2021.
  5. Leave a cell at 0 for any unit you do not need.
Add or subtract years, months and days to a date in Excel
The DATE formula turns 06/08/19 plus 1 year, 7 months and 15 days into 01/23/21; negative values subtract. (Image: Microsoft)

How to subtract dates in Excel to get months and years

DATEDIF counts complete months or years between two dates. Microsoft keeps it to support older Lotus 1-2-3 workbooks and warns that some of its units can return wrong results.

  1. Put the start date in A2 and the end date in B2.
  2. For complete months, type =DATEDIF(A2,B2,"m") in C2 and press Enter.
  3. For complete years, type =DATEDIF(A2,B2,"y") in D2 and press Enter.
  4. For months left over after the last full year, type =DATEDIF(A2,B2,"ym").
  5. For a decimal number of years, such as 0.58, type =YEARFRAC(A2,B2,1) instead.

The start date must come first in DATEDIF. If it is later than the end date, the formula returns #NUM!.

Subtract dates in Excel to get months with DATEDIF and the m unit
DATEDIF counts 28 complete months between 1/1/2014 and 5/6/2016; swap "m" for "y" to count complete years. (Image: Microsoft)

How to subtract dates in Excel to get age

Age is the number of complete years between a birth date and today. Add the months and days parts when you need a result such as 34 years, 2 months, 9 days.

  1. Type the birth date in A2.
  2. In B2, type =DATEDIF(A2,TODAY(),"y") and press Enter to get the age in years.
  3. In C2, type =DATEDIF(A2,TODAY(),"ym") to get the months past the last birthday.
  4. In D2, type =TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"m")) to get the days past the last full month.
  5. To show it all in one cell, type =B2&" years, "&C2&" months, "&D2&" days".

Avoid the DATEDIF "md" unit for the days part. Microsoft says it can return a negative number, a zero or an inaccurate result.

How to subtract dates in Excel from today

TODAY returns the current date from your computer's clock, so the result changes each day you open the workbook. Use it for countdowns, overdue days and time since an event.

  1. Type the past date in A2.
  2. Click B2 and type =TODAY()-A2 to get the days that have passed.
  3. For a future date, type =A2-TODAY() to get the days remaining.
  4. Press Enter. If the result shows as a date, press Ctrl + 1 and choose General.
  5. If the number does not change the next day, select File > Options > Formulas and set Calculation options to Automatic.

Subtract dates in Excel to get business days

NETWORKDAYS counts whole working days between two dates and skips Saturdays and Sundays. It can also skip a list of holidays you type into the sheet.

  1. Put the start date in A2 and the end date in A3.
  2. List each holiday in its own cell, for example A4 to A6.
  3. Click an empty cell and type =NETWORKDAYS(A2,A3,A4:A6).
  4. Press Enter. Microsoft's sample of 10/1/2012 to 3/1/2013 with three holidays returns 107.
  5. If your weekend is not Saturday and Sunday, use =NETWORKDAYS.INTL(A2,A3,2,A4:A6), where 2 sets Sunday and Monday as the weekend.
Subtract dates in Excel to get business days with NETWORKDAYS.INTL weekend codes
Typing the weekend argument opens a list of codes, where 1 means Saturday and Sunday and 2 means Sunday and Monday. (Image: Microsoft)

Subtracting dates and times in Excel to get hours and minutes

When each cell holds a date and a time, subtraction returns days with a decimal part. Multiply it, or apply a time format, to read the gap in hours or minutes.

  1. Type a full start date and time in A2, such as 6/9/2026 10:35 AM, with a space before AM or PM.
  2. Type the end date and time in B2.
  3. Type =B2-A2 in C2 and press Enter.
  4. Press Ctrl + 1, select Custom, type [h]:mm in the Type box and select OK to show total hours and minutes.
  5. For total hours as a number, use =(B2-A2)*24. For whole hours only, use =INT((B2-A2)*24).
  6. For total minutes, use =(B2-A2)*1440. For seconds, use =(B2-A2)*86400.

The square brackets in [h]:mm let the hour count pass 24. The plain h:mm format resets to zero after each full day.

Subtract dates and times in Excel to get the difference in days
The raw result 1.041666667 is in days; apply the [h]:mm custom format or multiply by 24 to read hours. (Image: Microsoft)

Subtract dates on different sheets in Excel

A formula can reference a date on another worksheet by putting the sheet name and an exclamation point before the cell address.

  1. Click the cell where the result should appear.
  2. Type = to start the formula.
  3. Select the tab of the sheet that holds the end date, then click the end date cell.
  4. Type -, select the tab of the sheet with the start date, and click that cell.
  5. Press Enter. The formula reads like =Sheet2!B2-Sheet1!A2.
  6. If a sheet name contains spaces or other non-letter characters, wrap it in single quotes, such as ='Q1 Dates'!B2-Sheet1!A2.

Tips: Subtracting dates in Excel

Tip Why it matters
Enter dates in a format Excel recognizes, such as 3/1/2026 Text that only looks like a date returns #VALUE! in a formula
Format the result cell as Number or General Otherwise Excel may show the day count as a date like 1/30/1900
Put the later date first in plain subtraction A negative result formatted as a date shows #####
Put the earlier date first in DATEDIF A start date after the end date returns #NUM!
Use EDATE for months, not 30 days Months have 28 to 31 days
Use [h]:mm for gaps longer than a day The plain h:mm format wraps at 24 hours
Build dates with DATE(year,month,day) inside formulas Microsoft notes that text dates can cause errors in functions such as NETWORKDAYS
Use absolute references such as $A$1 for one fixed date The reference stays put when you fill the formula down

Once the date formulas are in place, you can lock the formula cells so nobody overwrites them by accident.

How to tell if Excel recognizes a cell as a date

Most subtraction errors come from dates stored as text. A real date is a number underneath, so it behaves like one.

  1. Look at the alignment. With default formatting, real dates sit on the right of the cell and text dates sit on the left.
  2. Look for a small green triangle in the top-left corner of the cell. With error checking on, it can mark a text date that has a two-digit year.
  3. Select the cell, press Ctrl + 1 and choose General. A real date turns into a serial number such as 46082, while text stays the same.
  4. Press Ctrl + Z to put the date format back after the test.
Excel cell not recognized as a date, flagged as Text Date with 2-Digit Year
The green triangle opens this menu, where Convert XX to 20XX turns the text into a real date. (Image: Microsoft)

Why can't I subtract dates in Excel

The formula returns #VALUE!

One of the dates is stored as text, or it uses a day/month order that does not match your Windows regional settings.

  1. Click an empty cell formatted as General and type =DATEVALUE(A2), where A2 holds the text date.
  2. Press Enter to get the serial number, then copy it down for the other text dates.
  3. Copy the results, select the original text dates, press Ctrl + Alt + V, choose Values and select OK.
  4. Press Ctrl + 1, select Date and pick a format.
  5. If dates such as 13/04/2026 still fail, check that your Windows regional date format matches the order used in the sheet.

The cell shows #####

The result is a negative date, or the column is too narrow.

  1. Drag the right edge of the column header to widen the column.
  2. If it still shows #####, swap the order of the dates so the later date comes first.
  3. Or press Ctrl + 1 and change the cell to Number so a negative day count can display.

The result shows a date like 1/30/1900 instead of a number

Excel copied the date format from the source cells into the result cell.

  1. Select the result cell.
  2. Press Ctrl + 1 and choose Number or General under Category.
  3. Select OK. The cell now shows the day count.

DATEDIF returns #NUM!

The start date is later than the end date.

  1. Swap the first two arguments so the earlier date comes first.
  2. Or wrap both in MIN and MAX: =DATEDIF(MIN(A2,B2),MAX(A2,B2),"d").

The day count is off by a fraction or shows decimals

One or both cells hold a time as well as a date.

  1. Use =INT(B2)-INT(A2) to compare the dates only and drop the time part.
  2. Keep the decimal if you want the exact gap, and multiply it by 24 to read it in hours.
Why can't I subtract dates in Excel: #VALUE! from day-first text dates
21/10/2006 returns #VALUE! when Windows expects month first, while the same dates typed as 10/21/2006 return 6. (Image: Microsoft)

Subtracting dates in Excel FAQ

Can I subtract dates in Excel?

Yes. Excel stores dates as serial numbers, so a formula such as =B2-A2 returns the number of days between the two dates. Format the result cell as Number or General if it shows a date instead of a count.

What is the formula to subtract dates in Excel?

The basic formula is =end_date-start_date, for example =B2-A2. The DAYS function does the same job as =DAYS(B2,A2). For months or years, use =DATEDIF(A2,B2,"m") or =DATEDIF(A2,B2,"y").

How do I subtract dates in Excel to get days?

Type the later date in one cell and the earlier date in another, then enter =B2-A2. The result is the number of days. If it appears as a date, press Ctrl + 1 and change the format to General.

How do I subtract dates in Excel to get weeks?

Divide the day count by 7 with =(B2-A2)/7, then format the cell as Number with two decimal places. Use =INT((B2-A2)/7) if you only want complete weeks.

How do I subtract dates in Excel to find months?

Use =DATEDIF(A2,B2,"m"), with the earlier date first. It returns the number of complete months. Use the "ym" unit instead to get only the months left over after the last full year.

How do I subtract dates in Excel to get hours?

Subtract the two date-time cells and multiply by 24: =(B2-A2)*24. To show hours and minutes instead, use =B2-A2 and apply the custom format [h]:mm from the Format Cells box.

How do I subtract dates in Excel to get minutes?

Multiply the difference by 1,440, the number of minutes in a day: =(B2-A2)*1440. Format the result as Number so it does not display as a time.

Can I subtract times in Excel?

Yes. Subtract the start time from the end time with =B2-A2, then format the result with h:mm, or [h]:mm if the gap can pass 24 hours. Type each time with a space before AM or PM.

Can I calculate business days instead of calendar days?

Yes. =NETWORKDAYS(A2,B2) counts working days and skips Saturdays and Sundays. Add a holiday range as a third argument, such as =NETWORKDAYS(A2,B2,D2:D10), to skip holidays too.

Why is Excel showing a strange number when I subtract dates?

A five-digit number such as 46082 is a date serial number, which means the cell is formatted as General. A date like 1/30/1900 means the day count is formatted as a date. Change the format in Ctrl + 1.

If you are starting from scratch, open a new Excel workbook and type the sample dates from the first section to practise each formula.

Philip Celasco

Philip is a Texas-based technology writer and IT administrator at Techdows.com with more than 10 years of experience creating practical content for everyday users and professionals. He specializes in web browsers, particularly Chromium-based platforms such as Google Chrome, Microsoft Edge, Brave, and Opera. Through his work as an IT administrator, Philip has hands-on experience managing devices, configuring browser policies, troubleshooting software and network issues, and helping people resolve problems that affect productivity and security. His articles are based on practical testing and real-world technical experience. He covers browser settings, extensions, performance problems, privacy controls, security features, and Windows troubleshooting. Outside work, Philip enjoys the quieter side of life in Texas and stepping away from the screen when he can. He has two kids, two cats and loves to play golf with his mother during the weekends.

Leave a Reply

Your email address will not be published. Required fields are marked *