Intermediate

How to Remove the Time Portion From a Date Variable in VBA

In this article, I run a series of experiments to explore how different time-stripping approaches work in VBA.

Mike Wolfe

Dec 14, 2021 • 6 min read

The 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:

The DateValue function
The Int function
The Format function

Let's explore all three–along with a couple of other functions (CLng and CDate)–in a series of experiments.

Sample Code

The sample code takes a variant input and runs it through the following expressions:

DateValue(d)
Int(d)
Format(d, "m/d/yyyy")
CLng(d) and CDate(CLng(d))
CDate(d)

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

Five Approaches

The `DateValue` Function

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 Date value?

The `Int` Function

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

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

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 CDate(CLng(MyDate)).

The `CDate` Function

I included this line in the tests to show what happens when we explicitly coerce non-date types into the date data type.

Experimental Results

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.