Format returns a String while the other two return a Date. Date Literal with Time ComponentĭateValue, Int, and Format all return. Note that the Format function returns its value as a String, while the others are all returned as Dates. All five functions return the date we pass in. The right column is the actual value result. The middle column is the TypeName result. The left column shows which approach we are testing. In the results below, the top line shows the value we passed to the test function. I included this line in the tests to show what happens when we explicitly coerce non-date types into the date data type. 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)). That's why the 6 PM scenario below outputs for CLng, while all other approaches return for that same scenario. One interesting difference between CLng and Int is that CLng performs rounding while Int truncates (i.e., drops) any decimal portion. The CDate(CLng(MyDate)) approach is one that I've seen used occasionally in the wild. The long integer itself is not meaningful as a date, but we can coerce that long integer back into a date. The CLng function coerces the input value to a long integer with no decimal portion. Of course, what it returns is a Variant (String) and not a Date, though passing that result through the CDate() function would handle that. Interestingly, the Format function is the only one of the five approaches listed here that returns as its result for each scenario listed below (I'm not counting the Null scenario). You pass it a date or number and it will output a string in whatever format you requested. The Format function is kind of like the reverse of the DateValue function. 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 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. And, when passed a Date as input, the Int function returns a Variant (Date) as output (just like the DateValue function). Like DateValue, the Int function also returns a Variant. VBA datetime values are stored as doubles with the integer portion representing the date and the fractional portion representing the time.Īs a result, you can use the Int function to strip the time portion from a VBA datetime value. The Int function returns the integer portion of a number. Why not make DateValue a strongly-typed function that returns a Date value? The Int Function 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).
DateValue seems to return a Date for any valid input. The lone exception was passing Null to DateValue, which resulted in an Invalid use of Null error. And, indeed, in all of my tests below, TypeName() identified the results of the DateValue calculations as Date data types.
However, if date includes invalid time information (such as "89:98"), an error occurs.Īccording to official documentation, the DateValue function returns a Variant (Date). Notes from the DateValue documentation: If the date argument includes time information, DateValue doesn't return it. Sub TestTimeStripping(d As Variant)ĭebug.Print TypeName(DateValue(d)), DateValue(d)ĭebug.Print TypeName(Format(d, "m/d/yyyy")), Format(d, "m/d/yyyy")ĭebug.Print TypeName(CDate(CLng(d))), CLng(d) " "
TAKE TIME OUT OF DATE IN EXCEL CODE
The rest of the code is simply error-handling and immediate window output commands. The sample code takes a variant input and runs it through the following expressions:
TAKE TIME OUT OF DATE IN EXCEL SERIES
Let's explore all three–along with a couple of other functions ( CLng and CDate)–in a series of experiments. As it turns out, there are also many different approaches you can take to strip the time portion from a date. There are many situations where you will want to strip the time portion from a date.
The Date data type in VBA has both a date and time component to it.
0 kommentar(er)
