Excel VBA Date Subtraction: Get the Number of Days

In Excel VBA, subtract one date from another with days = endDate - startDate; for dates without times, the result is the number of days between them. If the values include times, subtraction can return a fraction of a day as well, so use DateDiff("d", startDate, endDate) when you specifically want the number of calendar-day boundaries crossed.

How date subtraction works in VBA

VBA stores a Date value as a serial number: its whole-number portion represents a date, and its fractional portion represents the time of day. Subtracting an earlier date from a later one therefore gives a number of elapsed days. For example, if the dates are three days apart and both have the same time, subtraction returns 3. If the later value is three days and six hours after the earlier one, the result is 3.25.

Use a Double variable for a subtraction result if times might be present. A Double can retain the fractional part. A Long is suitable only when you deliberately need a whole number and have decided how to handle any time component; converting a fractional result to an integer can change the value rather than simply display it without decimals.

The order matters. endDate - startDate is positive when the end date is later, zero when the values match, and negative when the end date is earlier. VBA does not automatically reorder the dates or make the result positive. If you need an absolute difference regardless of order, use Abs(endDate - startDate).

Subtract dates stored in worksheet cells

Suppose the start date is in A2 and the end date is in B2. In VBA, read those cells as dates and subtract them. The following macro puts the result in C2, provided both cells contain date values Excel can recognize:

Sub PutDaysBetweenDatesInC2()
    Dim startDate As Date
    Dim endDate As Date
    Dim days As Double

    ' Read the dates from A2 and B2.
    If IsDate(Range("A2").Value) And IsDate(Range("B2").Value) Then
        startDate = CDate(Range("A2").Value)
        endDate = CDate(Range("B2").Value)
        days = endDate - startDate
        Range("C2").Value = days
    Else
        MsgBox "Enter valid dates in A2 and B2."
    End If
End Sub

This code assumes the cells belong to the active worksheet. In a workbook with several sheets, qualify each Range with a worksheet, such as ThisWorkbook.Worksheets("Sheet1").Range("A2"), so the macro cannot accidentally read from whichever sheet happens to be active. Change the sheet name to match the workbook. Save a copy before running a macro: VBA macros cannot be undone with Ctrl+Z.

To enter and run the code in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and run the macro from the editor or Excel’s Developer > Macros command. If Developer is not visible, enable the Developer tab in Excel Options. On Mac, open the Visual Basic Editor using Option+F11 (some keyboards also require Fn), or use Tools > Macro > Visual Basic Editor. Menu labels and shortcut behavior can vary slightly by Mac keyboard configuration.

Use DateDiff when you mean calendar days

DateDiff is useful when the question is how many day changes occurred on the calendar, rather than how many 24-hour periods elapsed. With startDate and endDate already declared as Date variables, the expression DateDiff("d", startDate, endDate) returns the number of day boundaries between them. For example, moving from 11:59 p.m. on Monday to 12:01 a.m. on Tuesday crosses one calendar-day boundary, even though only two minutes elapsed.

That distinction matters when timestamps are involved. Direct subtraction in that example returns about 0.00139 days, because two minutes is roughly 1/720 of a day. DateDiff with the “d” interval returns 1. For two values that are just dates, with no meaningful time portions, the methods ordinarily give the same whole-day difference.

DateDiff does not make a difference inclusive. If a report should count both the first and last calendar dates, calculate the ordinary date difference and add one for a forward range. For a range from April 10 through April 12, subtracting gives 2 days between the dates, while an inclusive count is 3 dates. Decide what the business question means before adding one; elapsed duration and number of dates included in a range are different measures.

Choose a VBA function or a worksheet formula

If the result belongs in a worksheet and should update whenever the dates change, a formula is often simpler than a macro. With the start date in A2 and the end date in B2, enter =B2-A2 in C2. For dates on the same calendar day, the result is 0; for dates three days apart, it is 3. If the cells contain times, the result can include a decimal fraction. Format C2 as General or Number to see the numeric day count.

A VBA user-defined function is another option if you want a named function available in cells. Place this function in a standard module. With the start date in A2 and end date in B2, enter =DaysBetween(A2,B2) in a worksheet cell:

Function DaysBetween(startDate As Date, endDate As Date) As Double
    ' Return elapsed days, including any fraction from times.
    DaysBetween = endDate - startDate
End Function

A function is convenient for repeated calculations, but a workbook containing VBA must be saved in a macro-enabled format, such as .xlsm, to retain its code. Recipients may also need to enable macros according to their organization’s security rules. A regular worksheet formula needs no VBA and is generally easier to share when the calculation is just subtraction.

Check values, blanks, and date interpretation

A cell that looks like a date may actually contain text. Excel can display text that resembles a date, but VBA date conversion depends on whether the value can be interpreted as a date under the current settings. The macro example checks with IsDate before using CDate; that is more cautious than assuming every nonempty cell contains a valid date. It does not, however, decide what a blank should mean. Choose whether blanks should produce no result, zero, or a warning, and code that rule explicitly.

Ambiguous text such as 03/04/2026 can mean March 4 or April 3 depending on regional date conventions. For dates built in code, DateSerial(year, month, day) avoids that ambiguity. For example, DateSerial(2026, 4, 3) represents April 3, 2026. When importing dates, verify the source format and convert the values deliberately instead of relying on how a text string happens to be interpreted on a particular computer.

Also check whether the cells contain hidden time values. A cell formatted to show only a date can still contain a time, such as 12:00 p.m.; the display format hides it but subtraction does not. You can inspect the value by changing the cell’s number format to include a time. If only calendar dates matter, remove or ignore the time portion consistently before calculating, or use DateDiff with the “d” interval when its boundary-counting behavior matches the requirement.

Handle negative, inclusive, and workday counts

A negative answer is not an error by itself. It means the date supplied as the end is earlier than the start. To enforce chronological order, validate the dates and show a message when the end precedes the start. If order should not matter, use Abs around the subtraction. Do not use Abs when a negative result is useful for identifying overdue or reversed date ranges.

Calendar-day subtraction includes weekends and holidays. If the actual question is how many working days fall between two dates, date subtraction is the wrong measure. Excel’s worksheet functions NETWORKDAYS and NETWORKDAYS.INTL can count weekdays and optionally exclude holidays; VBA can call a worksheet function when appropriate. Clarify whether the start date and end date themselves count, which weekend pattern applies, and whether a holiday list is required. Workday counting follows a different convention from elapsed-day counting.

Similarly, subtracting dates does not automatically answer questions such as months between dates or years of age. Months have different lengths, so dividing a day count by 30 is only a rough estimate, not a reliable calendar-month calculation. Choose a method that matches the unit requested rather than treating every date difference as a simple number of days.

Common display and macro problems

The result displays as a date: The output cell may be formatted as Date. Change its format to General or Number to show the day count. The underlying calculation may already be correct.
The result has decimals: One or both inputs likely contain times. Use DateDiff with the “d” interval for crossed calendar days, or deliberately remove time values if the intended measure is whole dates.
The result is negative: Check the subtraction order and verify which cell contains the start date. Reverse the operands only if the desired sign convention requires it.
The macro cannot be found or run: Check that the code is in a standard module, macros are permitted, and the workbook is saved as a macro-enabled file. A worksheet function entered in a cell must also be available in that workbook.
The dates shift after copying data between workbooks: Excel workbooks can use different date systems. When transferring raw serial numbers rather than actual date values, verify the source and destination date systems and convert carefully.

For a single calculation, a worksheet formula avoids macro permissions and is easy to audit. Use VBA when the calculation is part of a repeatable process, such as filling results for many records or applying additional validation. Whichever approach you choose, format the result as a number and test it with a known date pair before relying on a larger report.

Frequently Asked Questions

Does VBA date subtraction return days or hours?
It returns a number measured in days. The fractional portion represents part of a day, so multiply the result by 24 if you need elapsed hours. For example, a difference of 0.5 is 12 hours.

Why does subtracting dates give a decimal?
At least one value likely includes a time. A difference of 1.5 means one and a half days, or 36 elapsed hours. If you need calendar dates crossed instead, use DateDiff("d", startDate, endDate).

How do I count both the start and end dates?
For a forward date-only range where both endpoints count, subtract the start from the end and add 1. Thus April 10 through April 12 counts as 3 dates. Do not add one when you want elapsed days.

How can I get the absolute number of days?
Use Abs(endDate - startDate) to discard the sign. This returns the magnitude of the elapsed-day difference, including any fractional time portion. Use DateDiff instead if you need an absolute count of calendar-day boundaries.

Can I calculate the result without VBA?
Yes. With the start date in A2 and end date in B2, enter =B2-A2 in another cell. Format the result as General or Number. The formula updates when either input changes and does not require macros.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.