DateDiff Function

Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

Parameters

interval: Required. String expression that is the interval of time you want to use to calculate the difference between date1 and date2. See Interval Settings for values.
date1, date2: Required. Variant (Date) values that you want to use in the calculation.
firstdayofweek: Optional. Constant that specifies the first day of the week. If not specified, Sunday is assumed. See FirstDayOfWeek Constants.
firstweekofyear: Optional. Constant that specifies the first week of the year. If not specified, the first week is assumed to be the week containing January 1. See FirstWeekOfYear Constants.

Interval Settings

The interval parameter can have the following values:

Setting	Description
"yyyy"	Year
"q"	Quarter
"m"	Month
"y"	Day of year
"d"	Day
"w"	Weekday
"ww"	Week of year
"h"	Hour
"n"	Minute
"s"	Second

`FirstDayOfWeek` Constants

Constant	Value	Description
vbUseSystem	0	Use system setting
vbSunday	1	Sunday (default)
vbMonday	2	Monday
vbTuesday	3	Tuesday
vbWednesday	4	Wednesday
vbThursday	5	Thursday
vbFriday	6	Friday
vbSaturday	7	Saturday

`FirstWeekOfYear` Constants

Constant	Value	Description
vbUseSystem	0	Use system setting
vbFirstJan1	1	Start with week containing January 1 (default)
vbFirstFourDays	2	Start with week having at least 4 days in new year
vbFirstFullWeek	3	Start with first full week of the year

Return Value

Returns a Long integer representing the number of intervals between the two dates. The result is positive if date2 is later than date1, negative if date2 is earlier than date1, and zero if they are equal.

Remarks

The DateDiff function is used to calculate the difference between two dates in the specified time interval. The function counts the number of interval boundaries crossed between the two dates. Important Characteristics: - Returns positive number if date2 > date1 (future date) - Returns negative number if date2 < date1 (past date) - Returns zero if date2 = date1 (same date/time) - Counts interval boundaries, not elapsed time - For "yyyy", crossing from Dec 31 to Jan 1 counts as 1 year - For "m", crossing from Jan 31 to Feb 1 counts as 1 month - For "ww", counts week boundaries (Sunday to Sunday by default) - Day of year ("y") is equivalent to day ("d") - Weekday ("w") is equivalent to day ("d")

Boundary Counting vs Elapsed Time

DateDiff counts boundaries crossed, not elapsed time:

' Year example
DateDiff("yyyy", #12/31/2024#, #1/1/2025#)  ' Returns 1 (crossed 1 year boundary)
' But only 1 day elapsed!
' Month example
DateDiff("m", #1/31/2025#, #2/1/2025#)  ' Returns 1 (crossed 1 month boundary)
' But only 1 day elapsed!
' Day example (actual elapsed time)
DateDiff("d", #1/1/2025#, #1/31/2025#)  ' Returns 30 (30 days elapsed)

Examples

Basic Usage

' Calculate days between dates
Dim days As Long
days = DateDiff("d", #1/1/2025#, #1/31/2025#)
MsgBox "Days: " & days  ' Shows 30
' Calculate months between dates
Dim months As Long
months = DateDiff("m", #1/15/2025#, #6/15/2025#)
MsgBox "Months: " & months  ' Shows 5
' Calculate years between dates
Dim years As Long
years = DateDiff("yyyy", #1/1/2000#, #1/1/2025#)
MsgBox "Years: " & years  ' Shows 25

Age Calculation

Function CalculateAge(birthDate As Date) As Integer
    Dim age As Integer
    age = DateDiff("yyyy", birthDate, Date)
    ' Adjust if birthday hasn't occurred this year
    If DateSerial(Year(Date), Month(birthDate), Day(birthDate)) > Date Then
        age = age - 1
    End If
    CalculateAge = age
End Function

Days Until/Since Event

Function DaysUntilEvent(eventDate As Date) As Long
    DaysUntilEvent = DateDiff("d", Date, eventDate)
End Function
' Usage
Dim daysLeft As Long
daysLeft = DaysUntilEvent(#12/25/2025#)
If daysLeft > 0 Then
    MsgBox daysLeft & " days until Christmas"
ElseIf daysLeft < 0 Then
    MsgBox "Christmas was " & Abs(daysLeft) & " days ago"
Else
    MsgBox "Today is Christmas!"
End If

Common Patterns

Elapsed Time Display

Function FormatElapsedTime(startTime As Date, endTime As Date) As String
    Dim hours As Long
    Dim minutes As Long
    Dim seconds As Long
    hours = DateDiff("h", startTime, endTime)
    minutes = DateDiff("n", startTime, endTime) Mod 60
    seconds = DateDiff("s", startTime, endTime) Mod 60
    FormatElapsedTime = hours & ":" & Format(minutes, "00") & ":" & Format(seconds, "00")
End Function

Working Days Calculator

Function CountWorkingDays(startDate As Date, endDate As Date) As Long
    Dim dayCount As Long
    Dim workDays As Long
    Dim currentDate As Date
    dayCount = DateDiff("d", startDate, endDate)
    workDays = 0
    For i = 0 To dayCount
        currentDate = DateAdd("d", i, startDate)
        If Weekday(currentDate) <> vbSaturday And Weekday(currentDate) <> vbSunday Then
            workDays = workDays + 1
        End If
    Next i
    CountWorkingDays = workDays
End Function

Overdue Indicator

Function GetOverdueDays(dueDate As Date) As Long
    Dim days As Long
    days = DateDiff("d", dueDate, Date)
    If days > 0 Then
        GetOverdueDays = days  ' Positive = overdue
    Else
        GetOverdueDays = 0     ' Not overdue
    End If
End Function

Subscription Status

Function GetSubscriptionStatus(startDate As Date, endDate As Date) As String
    Dim daysRemaining As Long
    daysRemaining = DateDiff("d", Date, endDate)
    Select Case daysRemaining
        Case Is < 0
            GetSubscriptionStatus = "Expired"
        Case 0 To 7
            GetSubscriptionStatus = "Expiring Soon (" & daysRemaining & " days)"
        Case 8 To 30
            GetSubscriptionStatus = "Active (" & daysRemaining & " days left)"
        Case Else
            GetSubscriptionStatus = "Active"
    End Select
End Function

Quarterly Report Period

Function GetQuartersBetween(startDate As Date, endDate As Date) As Integer
    GetQuartersBetween = DateDiff("q", startDate, endDate)
End Function
' Check if in same quarter
Function InSameQuarter(date1 As Date, date2 As Date) As Boolean
    InSameQuarter = (DateDiff("q", date1, date2) = 0)
End Function

Meeting Interval Tracker

Function WeeksSinceLastMeeting(lastMeeting As Date) As Long
    WeeksSinceLastMeeting = DateDiff("ww", lastMeeting, Date)
End Function
Function IsMeetingDue(lastMeeting As Date, interval As Integer) As Boolean
    IsMeetingDue = (DateDiff("ww", lastMeeting, Date) >= interval)
End Function

Time Tracking

Sub LogSessionDuration(startTime As Date, endTime As Date)
    Dim hours As Long
    Dim minutes As Long
    hours = DateDiff("h", startTime, endTime)
    minutes = DateDiff("n", startTime, endTime) - (hours * 60)
    Debug.Print "Session duration: " & hours & "h " & minutes & "m"
End Sub

Age Range Categorization

Function GetAgeCategory(birthDate As Date) As String
    Dim age As Integer
    age = DateDiff("yyyy", birthDate, Date)
    ' Adjust for birthday not yet occurred
    If Month(Date) < Month(birthDate) Or _
       (Month(Date) = Month(birthDate) And Day(Date) < Day(birthDate)) Then
        age = age - 1
    End If
    Select Case age
        Case 0 To 12
            GetAgeCategory = "Child"
        Case 13 To 19
            GetAgeCategory = "Teenager"
        Case 20 To 64
            GetAgeCategory = "Adult"
        Case Else
            GetAgeCategory = "Senior"
    End Select
End Function

Advanced Usage

Complete Time Breakdown

Type TimeBreakdown
    Years As Long
    Months As Long
    Days As Long
    Hours As Long
    Minutes As Long
    Seconds As Long
End Type
Function GetDetailedDifference(startDate As Date, endDate As Date) As TimeBreakdown
    Dim result As TimeBreakdown
    Dim tempDate As Date
    ' Calculate years
    result.Years = DateDiff("yyyy", startDate, endDate)
    tempDate = DateAdd("yyyy", result.Years, startDate)
    If tempDate > endDate Then
        result.Years = result.Years - 1
        tempDate = DateAdd("yyyy", result.Years, startDate)
    End If
    ' Calculate months
    result.Months = DateDiff("m", tempDate, endDate)
    tempDate = DateAdd("m", result.Months, tempDate)
    If tempDate > endDate Then
        result.Months = result.Months - 1
        tempDate = DateAdd("m", result.Months, DateAdd("yyyy", result.Years, startDate))
    End If
    ' Calculate remaining time
    result.Days = DateDiff("d", tempDate, endDate)
    result.Hours = DateDiff("h", tempDate, endDate) Mod 24
    result.Minutes = DateDiff("n", tempDate, endDate) Mod 60
    result.Seconds = DateDiff("s", tempDate, endDate) Mod 60
    GetDetailedDifference = result
End Function

Week Number with Custom First Day

Function GetWeekNumber(dateValue As Date, startDay As VbDayOfWeek) As Long
    Dim yearStart As Date
    yearStart = DateSerial(Year(dateValue), 1, 1)
    GetWeekNumber = DateDiff("ww", yearStart, dateValue, startDay, vbFirstFourDays)
End Function
' Usage
Dim weekNum As Long
weekNum = GetWeekNumber(Date, vbMonday)  ' ISO week number (Monday start)

Performance Timer

Private m_startTime As Date
Sub StartTimer()
    m_startTime = Now
End Sub
Function GetElapsedMilliseconds() As Double
    Dim seconds As Long
    seconds = DateDiff("s", m_startTime, Now)
    ' VB6 doesn't support milliseconds directly
    ' This gives seconds as closest approximation
    GetElapsedMilliseconds = seconds * 1000
End Function

Date Range Validator

Function ValidateDateRange(startDate As Date, endDate As Date, _
                         maxDays As Long) As Boolean
    Dim daysDiff As Long
    ' Check date order
    If startDate > endDate Then
        ValidateDateRange = False
        Exit Function
    End If
    ' Check range limit
    daysDiff = DateDiff("d", startDate, endDate)
    ValidateDateRange = (daysDiff <= maxDays)
End Function

Fiscal Period Calculator

Function GetFiscalPeriodDifference(date1 As Date, date2 As Date, _
                                  fiscalYearStart As Integer) As Long
    ' Calculate fiscal months between dates
    ' fiscalYearStart = month number (e.g., 4 for April)
    Dim adjustedDate1 As Date
    Dim adjustedDate2 As Date
    ' Adjust dates to fiscal year basis
    adjustedDate1 = DateSerial(Year(date1), Month(date1) - fiscalYearStart + 1, Day(date1))
    adjustedDate2 = DateSerial(Year(date2), Month(date2) - fiscalYearStart + 1, Day(date2))
    GetFiscalPeriodDifference = DateDiff("m", adjustedDate1, adjustedDate2)
End Function

Batch Date Comparison

Function FindOldestDate(dates() As Date) As Date
    Dim i As Integer
    Dim oldest As Date
    oldest = dates(LBound(dates))
    For i = LBound(dates) + 1 To UBound(dates)
        If DateDiff("d", dates(i), oldest) > 0 Then
            oldest = dates(i)
        End If
    Next i
    FindOldestDate = oldest
End Function

Error Handling

Function SafeDateDiff(interval As String, date1 As Variant, _
                     date2 As Variant) As Variant
    On Error GoTo ErrorHandler
    ' Validate dates
    If Not IsDate(date1) Or Not IsDate(date2) Then
        SafeDateDiff = Null
        Exit Function
    End If
    ' Validate interval
    Select Case LCase(interval)
        Case "yyyy", "q", "m", "y", "d", "w", "ww", "h", "n", "s"
            SafeDateDiff = DateDiff(interval, CDate(date1), CDate(date2))
        Case Else
            SafeDateDiff = Null
    End Select
    Exit Function
ErrorHandler:
    SafeDateDiff = Null
End Function

Common Errors

Error 5 (Invalid procedure call): Invalid interval string
Error 13 (Type mismatch): Non-date values passed as date parameters
Error 6 (Overflow): Result exceeds Long integer range

Performance Considerations

DateDiff is very fast for simple interval calculations
Day ("d") calculations are fastest
Month ("m") and year ("yyyy") require more computation
Week calculations depend on FirstDayOfWeek and FirstWeekOfYear parameters
For large datasets, cache DateDiff results when possible

Best Practices

Use Appropriate Intervals

' Good - Use "d" for exact day count
days = DateDiff("d", startDate, endDate)
' Be careful - "yyyy" counts year boundaries, not elapsed years
years = DateDiff("yyyy", #12/31/2024#, #1/1/2025#)  ' Returns 1, but only 1 day!

Order Matters

' Positive result - date2 is in future
diff = DateDiff("d", #1/1/2025#, #1/31/2025#)  ' Returns 30
' Negative result - date2 is in past
diff = DateDiff("d", #1/31/2025#, #1/1/2025#)  ' Returns -30

Handle Negative Results

Function GetAbsoluteDaysDifference(date1 As Date, date2 As Date) As Long
    GetAbsoluteDaysDifference = Abs(DateDiff("d", date1, date2))
End Function

Validate Date Order

Function CalculateDuration(startDate As Date, endDate As Date) As Long
    If startDate > endDate Then
        Err.Raise 5, , "Start date must be before end date"
    End If
    CalculateDuration = DateDiff("d", startDate, endDate)
End Function

Comparison with Other Functions

`DateDiff` vs `DateAdd`

' `DateDiff` - Calculate interval between dates (returns Long)
diff = DateDiff("d", #1/1/2025#, #1/31/2025#)  ' Returns 30
' `DateAdd` - Add interval to date (returns Date)
newDate = DateAdd("d", 30, #1/1/2025#)  ' Returns #1/31/2025#

`DateDiff` vs Subtraction

' Subtraction gives days as Double
diff = #1/31/2025# - #1/1/2025#  ' Returns 30.0
' DateDiff gives days as Long
diff = DateDiff("d", #1/1/2025#, #1/31/2025#)  ' Returns 30
' DateDiff supports other intervals
months = DateDiff("m", #1/1/2025#, #6/1/2025#)  ' Returns 5

Limitations

Result must fit in Long integer range (-2,147,483,648 to 2,147,483,647)
Week calculations depend on system or specified first day of week
Counts boundaries crossed, not elapsed time (except for "d", "h", "n", "s")
No built-in support for milliseconds
No built-in support for business day calculations
Cannot directly exclude holidays

DateAdd: Adds a time interval to a date
DatePart: Returns a specified part of a date
DateSerial: Creates a date from year, month, and day values
Year, Month, Day: Extract date components
Hour, Minute, Second: Extract time components
Weekday: Returns the day of the week
Now: Returns current date and time
Date: Returns current date
Time: Returns current time