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

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.

String Literal without Time

This is just like the previous section, except that now the CDate function also outputs the expected result.

Long Integer

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.

Double Value

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).

Null Values

No experiment of this type would be complete without understanding what happens to Null values.

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:

• CDate(Format(d, "yyyy-mm-dd"))

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 DateValue function in most situations where you need to remove the time portion from a VBA Date.

External references

Image by Monoar Rahman Rony from Pixabay