Date Comparison in VBA

Comparing dates in Microsoft Visual Basic for Applications is no different than comparing any other data type. The values you compare must be of type "Date." You can use the standard comparison operators: "<" for "less than"; "<=" for "less than or equal to"; ">" for "greater than"; ">=" for "greater than or equal to"; "=" for "equal to"; and "<>" for "not equal to." You can also use two comparison operators, "Is" and "IsNot."

It's helpful to remember how VBA stores date values in memory when you compare dates. A date is stored as a double-precision floating point number, or a Double. The long integer portion is the number of days, positive or negative, since December 30, 1899. The decimal portion is the amount of time that has elapsed since midnight. A date with no time has its decimal portion set to zero. For example, May 1, 1967 is stored as 24593.0. This means that when VBA compares two dates, it's actually comparing two numbers that represent days.

When you compare dates in VBA, you must ensure that the values you compare have a "Date" type and are not character strings. For example, the character string "05/01/1999" is less than the character string "12/31/1900" because "05" comes before "12." Convert a character string to a date type using the "CDate" function, for example, "CDate(TextBox.Value)".

In addition to CDate(), there are other helpful functions you might use when comparing dates. The "Date()" function provides today's date as a Date value. The "Now()" function provides today's date and time as a Date value. Because the Date data type also includes a time component, you can extract only the date portion of a variable when comparing dates by using the "DateValue()" function, for example, "If submission > DateValue(deadline) Then ..."

Because a date is stored as the number of days since December 30, 1899, you can include date arithmetic when comparing dates in VBA. For example, if a ticket expires three days after it was purchased, you can check for an expired ticket with VBA code similar to the following:

Public Function expired(purchase as Date) As Boolean expired = False If purchase+3 > Date() Then expired = True End If End Function