VBA Date Time Manipulation Calculation functions
Example
DateDiff()
DateDiff() returns a Long representing the number of time intervals between two specified dates.
Syntax
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
Examples
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()
DateAdd() returns a Date to which a specified date or time interval has been added.
Syntax
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
Dateor 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
