Excel VBA Date Difference: Calculate Days and Months

In Excel VBA, use DateDiff to count calendar boundaries between two dates, for example DateDiff("d", startDate, endDate) for days or DateDiff("m", startDate, endDate) for month boundaries. To get elapsed time as a number of days—including part of a day—subtract the two Date values instead: endDate - startDate.

Those approaches answer different questions. Choosing the right one matters when dates include times, cross a month or year boundary, or need to represent completed months rather than calendar changes.

What VBA DateDiff returns

DateDiff is a Visual Basic for Applications function that returns a whole-number count of intervals between a start date and an end date. Its general syntax is DateDiff(interval, date1, date2, [firstdayofweek], [firstweekofyear]). The interval is a text code, and the two date arguments can be variables, date expressions, or values Excel can interpret as dates.

For example, with startDate set to January 31, 2025 and endDate set to February 1, 2025, DateDiff("d", startDate, endDate) returns 1. So does DateDiff("m", startDate, endDate), because the dates fall in different calendar months. The month result does not mean a full month has elapsed; it counts a month boundary crossed.

Results are signed. Reversing the arguments reverses the direction: the same day calculation with February 1 first and January 31 second returns -1. This is useful when direction matters, but if a report must always show a positive duration, decide explicitly whether to reorder the dates or use an absolute value.

Choose the right interval code

The interval code determines what DateDiff counts. These are commonly useful options:

"d": calendar day boundaries.
"m": calendar month boundaries.
"yyyy": calendar year boundaries.
"q": calendar quarter boundaries.
"h", "n", and "s": hour, minute, and second boundaries. The code for minutes is "n", not "m".
"ww": week boundaries, with the first-day-of-week setting potentially affecting the result.

Be careful with intervals that sound similar. "y" means day of year, not year; use "yyyy" for calendar years. "w" is not a business-day counter. DateDiff does not automatically exclude Saturdays, Sundays, or holidays. For workday calculations, use a working-day method such as Excel’s NETWORKDAYS function with an appropriate holiday list, rather than treating a DateDiff result as business days.

Use subtraction for elapsed days and time

Excel stores dates as serial values, and a time is represented as a fraction of a day. Consequently, subtracting one date-time value from another gives a number of days that can include a fractional part. For instance, a difference of 1.5 means one day and twelve hours. DateDiff, by contrast, counts interval boundaries and returns an integer.

For an ordinary worksheet calculation, with a start date-time in A2 and an end date-time in B2, enter =B2-A2 in C2. If A2 is January 1, 2025 at 9:00 AM and B2 is January 2, 2025 at 9:00 PM, the result is 1.5 days. Format C2 as a number to see the decimal, or use a duration format such as [h]:mm to display the total hours and minutes. The square brackets allow hours to continue past 24 rather than restarting at zero.

In VBA, the equivalent subtraction is elapsedDays = endDate - startDate, where elapsedDays should be a numeric type such as Double if fractional days matter. For whole elapsed hours, multiply that result by 24; for minutes, multiply by 1,440. This computes the actual difference represented by the stored values, not a count of clock or calendar boundaries.

Calendar months and completed months are different

A frequent source of unexpected results is using DateDiff("m", ...) as if it measured the number of complete months. It does not. From January 31 to February 1, it returns 1 because the dates cross from January into February, even though only one day has elapsed. Similarly, from December 31 to January 1, DateDiff("yyyy", ...) returns 1 after only one day.

If a policy needs completed months—for example, tenure rounded down to the last anniversary—define the rule for month-end dates before writing the calculation. A common approach is to calculate the month-boundary difference and then check whether adding that many months to the start date would pass the end date. If it would, reduce the count by one. Month addition can itself have month-end behavior, so confirm how dates such as January 31 should be treated in the particular business rule. “Months between” has no single useful interpretation for every report.

Completed years have the same issue, especially for anniversaries involving February 29. Decide whether a leap-day anniversary is treated as February 28, March 1, or by another defined rule. A raw year-boundary count cannot make that policy decision for you.

A small VBA example

This macro compares two dates and prints both day and month-boundary differences in the Immediate window. It uses DateSerial to construct dates clearly, rather than relying on a text date that may be interpreted differently under regional settings.

Sub ShowDateDifferences()
    Dim startDate As Date
    Dim endDate As Date
    Dim dayBoundaries As Long
    Dim monthBoundaries As Long

    ' Build January 31 and February 1, 2025.
    startDate = DateSerial(2025, 1, 31)
    endDate = DateSerial(2025, 2, 1)

    dayBoundaries = DateDiff("d", startDate, endDate)
    monthBoundaries = DateDiff("m", startDate, endDate)

    Debug.Print "Days: "; dayBoundaries
    Debug.Print "Month boundaries: "; monthBoundaries
End Sub

The output is 1 day boundary and 1 month boundary. To view it in the VBA editor, open the Immediate window with Ctrl+G on Windows. In Excel for Microsoft 365 on Windows, Alt+F11 opens the VBA editor; on Mac, the editor shortcut can depend on the keyboard and function-key settings, and Excel for the web does not run VBA macros. Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

Week settings and first-week rules

DateDiff can take optional settings that affect week calculations. The firstdayofweek argument sets which weekday begins a week, and firstweekofyear specifies how the first week of a year is determined. If omitted, VBA uses its default settings. This matters when comparing dates around a week or year boundary: two different week conventions can produce different week counts for the same pair of dates.

For a reproducible report, specify the week rules rather than relying on defaults, and document which convention the audience expects. For example, calendar weeks that begin on Sunday and ISO-style weeks that begin on Monday do not always assign a date to the same numbered week. If the requirement is simply “how many complete seven-day periods elapsed,” subtract the dates and divide by seven, then decide whether to retain a fraction or round down. That is a different question from counting calendar-week boundaries.

Common problems and reliable date inputs

Most date-difference bugs come from ambiguous inputs or a mismatch between the desired measure and the chosen method. Use Date values when possible, rather than strings such as "03/04/2025", which can mean March 4 or April 3 depending on regional date conventions. In VBA, DateSerial(year, month, day) makes the intended components explicit.

Unexpected month or year result: DateDiff counts boundaries, not completed units. Check whether the dates merely crossed into a new calendar month or year.
Time disappears: An interval such as "d" returns an integer count. Subtract dates if the fraction of a day matters.
Negative result: DateDiff preserves the order of its arguments. Confirm which date is supposed to be the start.
Unexpected workday count: DateDiff includes calendar days; it does not remove weekends or holidays.
Invalid or missing dates: Check that input cells contain real Excel dates, not unrecognized text or blanks. Validate user input before passing it to a procedure.

Also remember that a displayed date is not necessarily the full stored value. A cell formatted to show only a date can still contain a time. That hidden time affects subtraction, even if the worksheet looks as though both values are whole dates.

Which method should you use?

Use DateDiff when the requirement is explicitly to count calendar boundaries—for example, days between date-only values, months crossed, or years crossed. Use subtraction when the result should represent elapsed time, including hours or fractions of days. For completed months or years, use a rule-based calculation that accounts for anniversaries and month ends. For working days, choose a business-calendar calculation that can exclude weekends and listed holidays.

In short, write down the question in ordinary language first: “How many date boundaries were crossed?”, “How many 24-hour periods elapsed?”, or “How many complete months passed?” Those are not interchangeable, and selecting the method from the wording prevents many apparent VBA errors.

Frequently Asked Questions

How do I calculate the number of days between two dates in VBA?
Use DateDiff("d", startDate, endDate) to count day boundaries. If the dates can include times and you need a fractional-day result, subtract them instead: endDate - startDate.

Why does DateDiff return 1 month for dates one day apart?
The dates may lie on opposite sides of a calendar-month boundary. DateDiff("m", ...) counts that boundary; it does not measure a complete month of elapsed time.

How do I get hours between two date-time values?
Subtract the start Date from the end Date and multiply the result by 24. In VBA, store the result in a numeric variable such as Double if it may include fractional hours.

Does DateDiff exclude weekends?
No. DateDiff counts calendar intervals and does not automatically remove weekends or holidays. Use a working-day calculation with an explicit holiday list when the question concerns business days.

Can I use VBA in Excel for the web?
No. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for VBA tasks.

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.