To subtract dates in Excel, put the later date first and type a formula such as =B2-A2. Excel returns the number of days between the two dates. If the answer looks like a date instead of a number, change the cell’s format to General or Number. These steps apply to Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac.
Before you start: how Excel stores dates
Excel doesn’t store a date as the text you see. It stores a serial number, a count of days. In the default 1900 date system, January 1, 1900 is serial number 1, and every later day adds one. A date format is the display layer that turns that number into something like 3/20/2026. Because every date is really a number, subtracting one date from another is ordinary arithmetic, and the result is a count of days.
That only works when both cells hold real dates. A quick test: a real date lines up on the right side of the cell by default, while a date stored as text lines up on the left. Text dates are the most common reason a date formula fails, so check your data first.
A few other terms come up below:
- Fill handle: the small square at the bottom-right corner of a selected cell. Drag it down to copy a formula to the rows beneath.
- Start date and end date: the earlier and later date in the calculation. Some functions want the start date first, and one wants the end date first, so the order matters.
- Format Cells: the dialog where you change how a number displays. Open it with Ctrl+1 on Windows or Command+1 on a Mac.
Subtract one date from another with the minus sign
The minus operator is the fastest route and the one to use for most day counts. These steps assume your start dates are in column A and your end dates are in column B, with headers in row 1.
- Type the earlier date in cell A2 and the later date in cell B2. Use a format Excel recognizes for your region, such as 1/5/2026 in the US.
- Click cell C2, where you want the answer to appear.
- Type
=B2-A2and press Enter. The later date goes first so the result is positive. - Look at the result. You should see a whole number, such as 74. If you see a date such as 3/14/1900 instead, Excel copied the date format into the answer cell. Select C2, open the Number Format box on the Home tab and choose General or Number.
- To repeat the formula for more rows, select C2 and drag the fill handle down. Each row updates to its own references, such as
=B3-A3and=B4-A4.
The same pattern works with a single column of dates or two whole columns of them. If you are lining up long lists side by side, the technique in subtracting one column from another in Excel carries over directly to dates.
Other ways to find the difference between two dates
The minus sign returns calendar days and nothing else. When you need years and months, working days only, or a count that runs from today, a function does the job better.
DAYS: the same answer, with a self-describing formula
The DAYS function takes the end date first and the start date second: =DAYS(B2,A2). It returns the same number as =B2-A2, but anyone reading the sheet later can see at a glance that the formula counts days. If you pass it text that looks like a date, it tries to convert the text for you. It returns #VALUE! when the text can’t be read as a date and #NUM! when a number is outside Excel’s valid date range.
DATEDIF: whole years, months or days
DATEDIF counts complete units between a start date and an end date. Its syntax is =DATEDIF(start_date, end_date, unit), so the earlier date goes first, the opposite of DAYS. The function exists for compatibility with older Lotus 1-2-3 workbooks, and Excel may not offer it in the formula suggestions as you type, so be ready to type the whole name yourself.
| Unit | What it returns | Example formula |
|---|---|---|
| “Y” | Complete years in the period | =DATEDIF(A2,B2,"Y") |
| “M” | Complete months in the period | =DATEDIF(A2,B2,"M") |
| “D” | Days in the period | =DATEDIF(A2,B2,"D") |
| “YM” | Months left over after whole years, ignoring days and years | =DATEDIF(A2,B2,"YM") |
| “YD” | Days left over, ignoring years | =DATEDIF(A2,B2,"YD") |
| “MD” | Days left over, ignoring months and years (not recommended) | =DATEDIF(A2,B2,"MD") |
“Complete” matters here. A period from June 1, 2023 to May 31, 2026 is one day short of three years, so the “Y” unit returns 2, not 3.
You can join several units into a readable sentence with the & operator:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"
Be careful with the “MD” unit. Microsoft documents that it can return a negative number, a zero or an inaccurate result, so avoid it for anything that matters. To get the days left after the last complete month, subtract the date that is that many whole months after the start date:
=B2-EDATE(A2,DATEDIF(A2,B2,"M"))
NETWORKDAYS: count working days only
Plain subtraction counts weekends and holidays. =NETWORKDAYS(A2,B2) returns only Monday through Friday. To skip holidays as well, list them in a range of cells (say F2:F4) and add that range as the third argument: =NETWORKDAYS(A2,B2,$F$2:$F$4). The dollar signs keep the holiday range fixed when you fill the formula down.
Note that NETWORKDAYS counts both the start date and the end date when they fall on workdays. From Monday to Friday of the same week, subtraction gives 4 but NETWORKDAYS gives 5.
If your weekend isn’t Saturday and Sunday, use NETWORKDAYS.INTL, which adds a weekend argument before the holidays: =NETWORKDAYS.INTL(A2,B2,7,$F$2:$F$4). Code 1 (the default) means Saturday and Sunday, 2 means Sunday and Monday, and 7 means Friday and Saturday. Codes 11 through 17 set a single weekend day, from 11 for Sunday to 17 for Saturday. You can also pass a seven-character string of 1s and 0s that starts with Monday, where 1 marks a day off, so "0000011" is a Saturday and Sunday weekend. Unlike DATEDIF, this function returns a negative number instead of an error if the start date is later than the end date.
TODAY: days since or until a date
TODAY() takes no arguments and returns the current date, updated each time the workbook recalculates. Use =TODAY()-A2 for days elapsed since a past date and =A2-TODAY() for days remaining until a future one. For someone’s age in whole years, use =DATEDIF(A2,TODAY(),"Y").
Because the value changes every day, a sheet built on TODAY() will show different numbers tomorrow. If you need a fixed date instead, press Ctrl+; (semicolon) on Windows to type today’s date into a cell as a static value. If TODAY() doesn’t seem to update on Windows, go to File > Options > Formulas and confirm that Automatic is selected under Calculation options.
Which one should you use?
| You want | Use | Argument order |
|---|---|---|
| Calendar days between two dates | =B2-A2 or =DAYS(B2,A2) | Later date first |
| Whole years or months | =DATEDIF(A2,B2,"Y") | Earlier date first |
| Business days, with optional holidays | =NETWORKDAYS(A2,B2,holidays) | Earlier date first |
| Days from or until today | =TODAY()-A2 | Later date first |
Subtract days, weeks, months or years from a date
Sometimes you don’t want the gap between two dates. You want an earlier date, such as a reminder 45 days before a deadline. Here, the answer cell should keep a date format.
Days and weeks
Subtract a plain number: =A2-45 returns the date 45 days before the date in A2. For weeks, multiply by 7 first: =A2-3*7 goes back three weeks. If the number of days changes from row to row, put it in its own column and reference it, as in =A2-C2. If you see a five-digit number such as 46000 instead of a date, the result is correct but needs a date format (Home tab, Number Format box, Short Date).
Months with EDATE
Months have different lengths, so subtracting 30 days isn’t the same as going back a month. EDATE handles that: =EDATE(A2,-3) returns the same day of the month three months earlier. A negative month count goes back in time, and a positive one goes forward. If the month count has a decimal, Excel drops the decimal part. When the target month is shorter, for example March 31 minus one month, EDATE should land on the last day of that month (February 28 or 29), so check a result or two if month-end dates matter. If the result shows as a number, apply a date format.
Years with DATE
To go back whole years, rebuild the date from its parts and subtract from the year: =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)). You can subtract years, months and days in one formula, such as =DATE(YEAR(A2)-1,MONTH(A2)-2,DAY(A2)-10), because DATE rolls extra or missing months and days into the neighboring month or year automatically.
That rollover has one side effect. February 29, 2024 minus one year asks for February 29, 2023, which doesn’t exist, so DATE returns March 1, 2023. =EDATE(A2,-12) returns February 28, 2023 instead. Pick the one that matches your rule for leap days.
Shift a whole column of dates without a formula
Paste Special can subtract a number of days from many dates at once and leave plain dates behind. Warning: this overwrites the original dates and leaves no formula to trace, so copy the sheet (or the column) before you start.
- In an empty cell, type the number of days to subtract, such as 15, and copy it with Ctrl+C (Command+C on a Mac).
- Select the date cells you want to change.
- On the Home tab, select the arrow under Paste and choose Paste Special. On Windows you can press Ctrl+Alt+V instead.
- Under Paste, choose Values so the helper cell’s number format doesn’t replace your date format. Under Operation, choose Subtract, then select OK.
- Check that each date moved back 15 days, then delete the helper cell. If the dates turned into five-digit numbers, apply Short Date from the Number Format box.
Worked example: a small project tracker
Suppose three people each run a project, and you want the length of each in calendar days, working days and whole months. The start dates are in column A, the end dates in column B, and one holiday (May 25, 2026) is in cell F2. All of the start dates are Mondays and all of the end dates are Fridays. The data is fictional.
| Owner | Start (A) | End (B) | Days =B2-A2 | Workdays =NETWORKDAYS(A2,B2,$F$2) | Months =DATEDIF(A2,B2,"M") |
|---|---|---|---|---|---|
| Alex | 1/5/2026 | 3/20/2026 | 74 | 55 | 2 |
| Jordan | 2/16/2026 | 7/31/2026 | 165 | 119 | 5 |
| Priya | 11/3/2025 | 6/12/2026 | 221 | 159 | 7 |
A few things to notice. Alex’s project ends before the May holiday, so the holiday changes nothing for that row. Jordan’s and Priya’s projects span May 25, so each loses one workday compared with =NETWORKDAYS(A2,B2) without the holiday argument (120 and 160). The workday counts include both the first Monday and the last Friday, while the day counts don’t include the start date. And Priya’s 221 days come out to 7 whole months, because June 12 falls after June 3, the seven-month mark.
To send Alex a reminder 45 days before the deadline, enter =B2-45 in a date-formatted cell. It returns 2/3/2026.
When the result looks wrong
The answer is a date, not a number
Excel often formats the answer cell like the cells it references. A result such as 1/14/1900 is really the number 14 in a date format. Change the cell to General or Number.
The cell fills with ##### symbols
Two causes are common. The column may be too narrow, which you fix by dragging the right edge of the column header wider. Or the formula returned a negative number in a date-formatted cell, because the earlier date came first. Excel can’t display a negative date in the 1900 date system, so either swap the order of the dates or format the cell as General to see the negative count. If you always want a positive gap regardless of order, wrap the formula in ABS, as in =ABS(B2-A2).
#VALUE! appears
One of the cells probably holds text, not a date. Look for left alignment or, for two-digit years, a small green triangle in the corner of the cell. To convert a text date, enter =DATEVALUE(A2) in a blank cell set to General. That returns a serial number you can copy and paste back as values, then format as a date. DATEVALUE accepts dates from January 1, 1900 through December 31, 9999. Dates typed in a different regional order (day first instead of month first) can also produce text instead of dates.
#NUM! or #NAME? appears
DATEDIF returns #NUM! when the start date is later than the end date, so swap the arguments. #NAME? usually means a misspelled function name. Because DATEDIF may not appear in the suggestion list, it’s easy to type something like DATEIF by mistake.
The count is off by one
Subtraction measures the gap between two dates, so January 1 to January 2 is 1 day. If your rule counts both the first and the last day, as for hotel nights versus calendar days or event days, add 1: =B2-A2+1.
The result has a decimal
A cell that holds a date and a time stores the time as a fraction of a day, so 6:00 PM adds 0.75. Subtracting two such values gives days plus a fraction. Use =INT(B2-A2) for whole days or =(B2-A2)*24 for total hours. For more on time-of-day math, see how to subtract times and show hours and minutes in Excel.
Dates from another workbook are four years off
Excel has two date systems. The 1900 system is the default in Excel for Windows and in current Excel for Mac, while some older Mac workbooks use the 1904 system, which counts from January 1, 1904. The same date’s serial number differs by 1,462 days between the two. If you copy dates between workbooks that use different systems, they shift by about four years. On Windows, check the setting at File > Options > Advanced, under “When calculating this workbook,” in the Use 1904 date system box.
A copied formula won’t calculate
Formulas copied from a web page or a word processor can contain a typographic dash instead of a real minus sign, which Excel may not read as subtraction. Retype the minus sign with the hyphen key on your keyboard or the minus key on the number pad.
Confirm the result, and undo it if needed
For a quick check, put =DAYS(B2,A2) next to your subtraction formula. The two should match. For a short range you can also count on a calendar. For workday counts, remember that NETWORKDAYS includes both ends, so a Monday-to-Friday week should return 5.
To see the serial number behind any date, change its format to General. That is useful for spotting text dates, which won’t change at all. To reverse a mistake right away, press Ctrl+Z (Command+Z on a Mac). Undo history is lost once you close the workbook, which is why the backup copy matters before a Paste Special subtraction. If a Paste Special change is already saved, repeat it with Add instead of Subtract and the same number to put the dates back.
More questions about date subtraction
How do I get the number of weeks between two dates?
Divide the day count by 7: =(B2-A2)/7. Format the result with two decimal places to see partial weeks, or wrap it in INT for whole weeks only.
How do I show years, months and days in one cell?
Combine “Y” and “YM” from DATEDIF with the EDATE workaround for leftover days, so you avoid the unreliable “MD” unit:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&B2-EDATE(A2,DATEDIF(A2,B2,"M"))&" days"
For Priya’s project above, that returns “0 years, 7 months, 9 days.”
Can I type dates straight into the formula?
Yes, but use the DATE function so Excel reads them the same way in every region: =DATE(2026,3,20)-DATE(2026,1,5) returns 74. Cell references are still easier to update later.
Can Excel subtract dates before 1900?
Not with normal date values. The 1900 date system starts on January 1, 1900, so Excel stores anything earlier as text, and text dates can’t be subtracted.
Does this work the same way in Google Sheets?
The minus sign, DAYS, DATEDIF and NETWORKDAYS all exist there too, with a few differences in menus and formatting. The walkthrough on finding the days between dates in Google Sheets covers those.
Next step
Start with =B2-A2 on your own data and set the answer cell to General. Once that works, swap in NETWORKDAYS for business days or DATEDIF for whole months and years. The full argument details are on the DATEDIF function reference and the NETWORKDAYS.INTL reference. For the formula basics behind all of this, start from the basics of subtraction in Excel.