VBA Date Time Manipulation Calculation functions



DateDiff() returns a Long representing the number of time intervals between two specified dates.


DateDiff ( interval, date1, date2  [, firstdayofweek] [, firstweekofyear] )
  • interval can be any of the intervals defined in the DatePart() function
  • date1 and date2 are the two dates you want to use in the calculation
  • firstdayofweek and firstweekofyear are optional. Refer to DatePart() function for explanations


Sub DateDiffExamples()

    ' Check to see if 2016 is a leap year.
    Dim NumberOfDays As Long
    NumberOfDays = DateDiff("d", #1/1/2016#, #1/1/2017#)
    If NumberOfDays = 366 Then
        Debug.Print "2016 is a leap year."              'This will output.
    End If
    ' Number of seconds in a day
    Dim StartTime As Date
    Dim EndTime As Date
    StartTime = TimeSerial(0, 0, 0)
    EndTime = TimeSerial(24, 0, 0)
    Debug.Print DateDiff("s", StartTime, EndTime)       'prints 86400

End Sub


DateAdd() returns a Date to which a specified date or time interval has been added.


DateAdd ( interval, number, date  ) 
  • interval can be any of the intervals defined in the DatePart() function
  • number Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
  • date is a Date or literal representing date to which the interval is added

Examples :

Sub DateAddExamples()

    Dim Sample As Date
    'Create sample date and time of 2016-07-28 12:34:56
    Sample = DateSerial(2016, 7, 28) + TimeSerial(12, 34, 56)
    ' Date 5 months previously (prints 2016-02-28):
    Debug.Print Format$(DateAdd("m", -5, Sample), "yyyy-mm-dd")
    ' Date 10 months previously (prints 2015-09-28):
    Debug.Print Format$(DateAdd("m", -10, Sample), "yyyy-mm-dd")
    ' Date in 8 months (prints 2017-03-28):
    Debug.Print Format$(DateAdd("m", 8, Sample), "yyyy-mm-dd")

    ' Date/Time 18 hours previously (prints 2016-07-27 18:34:56):
    Debug.Print Format$(DateAdd("h", -18, Sample), "yyyy-mm-dd hh:nn:ss")
    ' Date/Time in 36 hours (prints 2016-07-30 00:34:56):
    Debug.Print Format$(DateAdd("h", 36, Sample), "yyyy-mm-dd hh:nn:ss")

End Sub