How to Subtract Dates in Excel (9 Methods for Days, Months, and Business Days)

Why Date Subtraction Works at All

Excel stores every date as a serial number counted from 1 January 1900, which is serial number 1. So 1 January 2026 is stored as 46023, and the date format layered on top is presentation only. Because dates are numbers underneath, a plain minus sign works on them, which is why simple subtraction produces a day count with no special date function required.

Understanding how Excel stores a date value explains almost every problem that follows, especially results displayed as dates instead of whole numbers, or dates that refuse to calculate because they are stored as text data. Reports built on top of these calculations stay cleaner when the formulas below are paired with conditional formatting rules and data validation dropdowns, so only a valid date ever reaches the source columns.

Method 1: The Minus Sign (Fastest Way to Subtract Cells)

To begin, type the following formula in an empty cell:

=B2-A2

Press Enter, and the answer appears in one cell, with the following formula visible in the formula bar. Drag the fill handle at the bottom-right corner down the column to subtract cells across every row.

Use this when the answer needed is a straightforward count of days. It handles dates in any order, though subtracting a future date from a past date returns a negative number. Wrapping the formula in ABS() forces a positive result: =ABS(B2-A2). Dividing by 7 converts the answer into weeks: =(B2-A2)/7.

Method 2: The DAYS Function

=DAYS(B2,A2)

This date function accepts the end date first and the start date second. The output matches Method 1 exactly, so the choice comes down to readability. Formulas that other people audit tend to benefit from named arguments, since DAYS states its intent where a bare minus sign leaves it implied.

DAYS also accepts date strings directly, which helps for one-off checks on how many days sit between two dates: =DAYS("2026-12-31","2026-01-01") returns 364.

Method 3: The DATEDIF Function for Years, Months, and Days

The DATEDIF function is a legacy tool kept for Lotus 1-2-3 compatibility. It never appears in the autocomplete list, so type it in full. It remains the standard way to find days, months, or full years between start and end dates, and it is the fastest route to calculate the difference in units other than days.

=DATEDIF(A2,B2,"y")     'complete years
=DATEDIF(A2,B2,"m")     'complete months
=DATEDIF(A2,B2,"d")     'number of days
=DATEDIF(A2,B2,"ym")    'months remaining after whole years
=DATEDIF(A2,B2,"md")    'days remaining after whole months

To calculate age or staff tenure as a readable string, combine three calls:

=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days"

To calculate age against today's date, nest the TODAY function inside the formula so the result updates every time the sheet opens:

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

Note that DATEDIF requires the start date first. Reversing the arguments returns #NUM! rather than a negative number.

Method 4: NETWORKDAYS to Exclude Weekends and Holidays

Project timelines and due dates usually need working days rather than calendar days, so the plain minus sign gives incorrect results here.

=NETWORKDAYS(A2,B2)

This will exclude weekends automatically and count both endpoints. To skip public holidays as well, list the holiday dates in a separate range and pass that range as the third argument:

=NETWORKDAYS(A2,B2,$F$2:$F$12)

For regions where the weekend falls on different days, NETWORKDAYS.INTL accepts a weekend code. Code 7 means Friday and Saturday, code 11 means Sunday only:

=NETWORKDAYS.INTL(A2,B2,7,$F$2:$F$12)

Locking the holidays range with absolute cell references keeps it fixed as the formula copies down.

Method 5: YEARFRAC for Fractional Years

Finance and HR calculations often need a decimal year rather than a whole one.

=YEARFRAC(A2,B2)

A gap of 18 months returns roughly 1.5. The optional fourth argument sets the day count basis, where 0 is US , 1 is actual/actual, and 3 is actual/365. Interest and accrual work usually specifies which basis applies.

Method 6: Subtracting Dates that include a Time Value

When cells hold full timestamps, subtraction returns a decimal, because a time value is stored as the fractional part of the serial number. Half a day is equal to 0.5.

To convert the gap between a start time and an end time into hours:

=(B2-A2)*24

For minutes, multiply by 1440. For elapsed time that runs past 24 hours, subtract normally, then open the Format Cells dialog box, choose the Number tab, select Custom, and enter [h]:mm. The square brackets stop Excel from rolling the hour count over at 24.

Method 7: Subtracting a Fixed Number of Days from a Specific Date

Rather than comparing two columns, this shifts a single specific date backwards.

=A2-30

To find the current date minus 30 days, use the TODAY function instead of a cell reference: =TODAY()-30.

For a fixed number of months, EDATE adjusts for month length correctly, including February:

=EDATE(A2,-3)

EOMONTH returns the last day of a month offset from the original date, which suits period-end reporting:

=EOMONTH(A2,-1)

Method 8: Paste Special for a One-Time Shift

To adjust an entire column of dates by a set number of days without adding a formula column:

1. Type the number of days into any blank cell and copy it.
2. Select the date range to change.
3. Right-click and choose Paste Special.
4. Select Values under Paste and Subtract under Operation.
5. Click OK.

Every selected date moves back by that amount in place. This rewrites the original values, so work on a copy of the sheet.

Method 9: Advanced Techniques with a VBA Macro

Workbooks that need the same calculations every week can hand the heavy lifting to a short macro. Press Alt + F11 to open the Visual Basic Editor, choose Insert > Module, and paste:

Sub SubtractDates()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If IsDate(ws.Cells(i, 1)) And IsDate(ws.Cells(i, 2)) Then
            ws.Cells(i, 3).Value = DateDiff("d", ws.Cells(i, 1), ws.Cells(i, 2))
            ws.Cells(i, 3).NumberFormat = "General"
        End If
    Next i
End Sub

Run it with F5. Column C fills with the day difference for every row holding two valid dates. Save the file as .XLSM, since macros are stripped from a standard .XLSX file.

Common Issues and Troubleshooting

The result shows as a date instead of a number. Excel copied the number format from the source cells. Press Ctrl + 1, open the Number tab, and set the number format to General.

The formula returns #VALUE!. One of the cells holds text that resembles a date. Text entries align left by default while real dates align right, which makes incorrect results easy to spot. Fix them with Data > Text to Columns, choose Delimited, click Next twice, select Date with the matching order such as DMY, and finish. =DATEVALUE(A2) converts individual entries.

The answer is off by one day: Plain subtraction measures the gap between two dates rather than counting the dates themselves. Add 1 to include both endpoints: =B2-A2+1. NETWORKDAYS already includes both.

DATEDIF returns #NUM!: The start date falls after the end date. Swap the arguments.

Dates in a CSV import all read as text - The regional date format in the file differs from the system setting. Text to Columns with an explicit date order resolves it. Where imports break repeatedly, the guide to breaking external links in Excel covers related cleanup on inherited workbooks.

Results changed after copying the formula down - Holiday ranges and other lookup ranges need absolute cell references. Press F4 while the reference is selected to lock it as $F$2:$F$12.

Dates before 1900 return errors. The serial number system starts on 1 January 1900 and cannot represent an earlier past date as a numeric value. Historical work of that kind needs a text-based or external approach.

Calculating Date Differences Programmatically with IronXL

Manual formulas work well for one workbook. Recurring reports, batch exports, and server-side processing call for something that runs without Excel installed. IronXL reads and writes spreadsheet files directly from C# and applies date arithmetic across thousands of rows in a single pass.

using IronXL;
using System;

WorkBook workbook = WorkBook.Load("timesheets.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

for (int row = 2; row <= 500; row++)
{
    DateTime start = sheet[$"A{row}"].DateTimeValue;
    DateTime end = sheet[$"B{row}"].DateTimeValue;

    sheet[$"C{row}"].Value = (end - start).TotalDays;
}

workbook.SaveAs("timesheets-calculated.xlsx");

using IronXL;
using System;

WorkBook workbook = WorkBook.Load("timesheets.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

for (int row = 2; row <= 500; row++)
{
    DateTime start = sheet[$"A{row}"].DateTimeValue;
    DateTime end = sheet[$"B{row}"].DateTimeValue;

    sheet[$"C{row}"].Value = (end - start).TotalDays;
}

workbook.SaveAs("timesheets-calculated.xlsx");

Imports IronXL
Imports System

Dim workbook As WorkBook = WorkBook.Load("timesheets.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

For row As Integer = 2 To 500
    Dim start As DateTime = sheet($"A{row}").DateTimeValue
    Dim end As DateTime = sheet($"B{row}").DateTimeValue

    sheet($"C{row}").Value = (end - start).TotalDays
Next

workbook.SaveAs("timesheets-calculated.xlsx")

The library runs on .NET without Microsoft Office or Interop, which makes it a fit for scheduled jobs, web applications, and automated reporting pipelines.

Final Thoughts

Simple subtraction answers most questions about dates in Excel. The DATEDIF function covers months and full years, NETWORKDAYS handles working days and holidays, YEARFRAC serves financial calculations, and Paste Special shifts whole columns at once. Formatting accounts for nearly every error along the way, and setting the result cell to General through the Format Cells dialog box clears the most common one.

For teams producing the same date calculations on a schedule, moving the logic into code removes the manual step entirely. Explore IronXL for .NET to see how spreadsheet automation fits an existing workflow, or browse the wider Excel tutorials library for related tasks such as applying cell borders and building Gantt charts from start and end dates. You can also download a free trial to explore its features in your own projects.