Application Evaluate

Though not normally considered to be a conversion function, Application.Evaluate is the only way to convert a U.S.-formatted date string to a date number. The following two functions, isDateUS and DateValueUS, are wrapper functions that use this method.

The IsDateUS Function

The built-in isDate function validates a string against the Windows Regional Settings. This function provides you with a way to check if a string contains a U.S.-formatted date:

Function IsDateUS(sDate As String) As Boolean

IsDateUS = Not IsError(Application.Evaluate("DATEVALUE(""" & _

End Function sDate is a string containing a U.S.-formatted date. IsDateUS returns True if the string contains a valid U.S. date, and False if not.

The DateValueUS Function

The VBA DateValue function converts a string formatted according to the Windows Regional Settings to a Date type. This function converts a string containing a U.S.-formatted date to a Date type. If the string cannot be recognized as a U.S.-formatted date, it returns an Error value that can be tested for, using the IsError function:

Function DateValueUS(sDate As String) As Variant

DateValueUS = Application.Evaluate("DATEVALUE(""" & sDate & """)") End Function sDate is a string containing a U.S.-formatted date. DateValueUS returns the date value of the given string, in a Variant (because it may contain an error value if sDate is not a valid date string).

0 0

Post a comment