Date data type in VBA has both a date and time component to it.
There are many situations where you will want to strip the time portion from a date. As it turns out, there are also many different approaches you can take to strip the time portion from a date.
The three most common approaches are:
Let's explore all three–along with a couple of other functions (
CDate)–in a series of experiments.
The sample code takes a variant input and runs it through the following expressions:
The rest of the code is simply error-handling and immediate window output commands.
Sub TestTimeStripping(d As Variant) On Error Resume Next Debug.Print "DateValue: "; Debug.Print TypeName(DateValue(d)), DateValue(d) If Err.Number <> 0 Then Debug.Print "ERROR: "; Err.Description Err.Clear End If Debug.Print "Int: "; Debug.Print TypeName(Int(d)), Int(d) If Err.Number <> 0 Then Debug.Print "ERROR: "; Err.Description Err.Clear End If Debug.Print "Format: "; Debug.Print TypeName(Format(d, "m/d/yyyy")), Format(d, "m/d/yyyy") If Err.Number <> 0 Then Debug.Print "ERROR: "; Err.Description Err.Clear End If Debug.Print "CLng: "; Debug.Print TypeName(CDate(CLng(d))), CLng(d); " [" & CDate(CLng(d)) & "]" If Err.Number <> 0 Then Debug.Print "ERROR: "; Err.Description Err.Clear End If Debug.Print "CDate: "; Debug.Print TypeName(CDate(d)), CDate(d) If Err.Number <> 0 Then Debug.Print "ERROR: "; Err.Description Err.Clear End If End Sub
Notes from the DateValue documentation:
If the date argument includes time information, DateValue doesn't return it. However, if date includes invalid time information (such as "89:98"), an error occurs.
According to official documentation, the DateValue function returns a Variant (Date). And, indeed, in all of my tests below, TypeName() identified the results of the DateValue calculations as
Date data types.
The lone exception was passing Null to DateValue, which resulted in an Invalid use of Null error. I find this odd. DateValue seems to return a Date for any valid input. In my mind, then, the only reason to have DateValue return a Variant would be if you wanted to support
DateValue(Null) returning a Null (as opposed to erroring out). Why not make DateValue a strongly-typed function that returns a
The Int function returns the integer portion of a number. VBA datetime values are stored as doubles with the integer portion representing the date and the fractional portion representing the time.
As a result, you can use the Int function to strip the time portion from a VBA datetime value.
Like DateValue, the Int function also returns a Variant. And, when passed a Date as input, the Int function returns a Variant (Date) as output (just like the DateValue function).
The one critical difference between Int and DateValue is that Int will raise an error if you pass it any string, even one that is obviously formatted as a date. This makes sense, as Int is a numeric function, while the primary purpose of the DateValue function is for it to convert strings to dates.
The Format function is kind of like the reverse of the DateValue function. You pass it a date or number and it will output a string in whatever format you requested.
Interestingly, the Format function is the only one of the five approaches listed here that returns 12/31/2021 as its result for each scenario listed below (I'm not counting the Null scenario). Of course, what it returns is a Variant (String) and not a Date, though passing that result through the CDate() function would handle that.
The CLng function coerces the input value to a long integer with no decimal portion.
The long integer itself is not meaningful as a date, but we can coerce that long integer back into a date. The
CDate(CLng(MyDate)) approach is one that I've seen used occasionally in the wild.
One interesting difference between CLng and Int is that CLng performs rounding while Int truncates (i.e., drops) any decimal portion. That's why the 6 PM scenario below outputs 1/1/2022 for CLng, while all other approaches return 12/31/2021 for that same scenario.
To add value to the experimental results, I display both the raw results of calling
CLng(MyDate) as well as the date-coerced results of calling
I included this line in the tests to show what happens when we explicitly coerce non-date types into the date data type.
In the results below, the top line shows the value we passed to the test function. The left column shows which approach we are testing. The middle column is the TypeName result. The right column is the actual value result.
Date Literal without Time
This is our baseline. All five functions return the date we pass in. Note that the Format function returns its value as a String, while the others are all returned as Dates.
Date Literal with Time Component
DateValue, Int, and Format all return 12/31/2021. Format returns a String while the other two return a Date.
String Literal with Time Component
The Int and CLng functions fail on this one because they cannot convert date/time-formatted Strings. Only DateValue and Format output the expected results.
String Literal without Time
This is just like the previous section, except that now the CDate function also outputs the expected result.
It's generally a bad idea to do any sort of date manipulation on the low-level numerical representation of VBA dates.
That said, I thought it would be interesting to see how each approach would respond if we passed it a plain long integer.
Here's a different number. This one includes a decimal portion that happens to coincide with 6 PM (0.75 x 24 hours = 18 => 6 PM).
No experiment of this type would be complete without understanding what happens to
Results and Recommendations
So which of the five approaches is the best?
The simple answer is, "It depends." And, while technically true, that answer feels like a copout.
The most flexible approach if you want to support as many input types as possible, is probably to use the
Format function like so:
This feels overly permissive, though. I'm not sure the fact that it can convert a long integer like 44,561 into a date is necessarily a good thing.
So, what is my bottom line recommendation?
I recommend using the
DateValuefunction in most situations where you need to remove the time portion from a VBA Date.