Date and Time Handling
- Last UpdatedJul 18, 2023
- 2 minute read
VBA has several pre-defined date and time functions that return date/time values. There are three functions enabling you to determine the current date and time set in Windows. The Now function, Date function, and Time function check your system clock and return all or part of the current setting.
To retrieve the date portion of a date/time value, use the pre-defined DateValue function. This function takes in either a string or a date value and returns only the date portion.
Using DateValue, you can compare the date portion of a date variable to a specific date value, like this:
If DateValue(dtmSomeDate) = #5/14/70# Then
' You know the date portion of dtmSomeDate is 5/14/70
End If
If you need just the time portion of a date variable, use the TimeValue function. Using this function, you could write code that checks the time portion of a date variable against a particular time, like this:
If TimeValue(dtmSomeDate) > #1:00 PM# Then
' You know the date variable contained a date portion
' with a time after 1:00 PM.
End If
You can perform arithmetic or mathematics (math) on date/time values because VBA stores dates internally as serial values. Adding or subtracting integers adds or subtracts days, while adding or subtracting fractions adds or subtracts time. Therefore, adding 20 to a date value in VBA adds 20 days, while subtracting 1/24 subtracts one hour. For example, to get tomorrow's date, you could just add 1 to today's date, like this:
dtmTomorrow = Date()+ 1
Date is a built-in VBA function that returns the date portion (the integer part) of the current date and time retrieved from the Windows operating system. Adding 1 to that value returns a date that represents the next day.
The same mechanism works for subtracting two dates. Although VBA supplies the DateDiff function for finding the interval spanned by two date/time values, if you just need to know the number of days between the two dates, you can simply subtract one from the other.