Issue while comparing dates in VBA?

Question

I am trying to compare two dates using the below code:

MsgBox (Format("07-12-2018", "dd-MMM-yyyy") > Format("31-12-2016", "dd-MMM-yyyy"))

Though the first date is greater than the second, the Message box displays False

Do not compare string representations of dates. Compare dates. Parse "07-12-2018" and "31-12-2016" into variables of type Date and compare that. — GSerg
– GSerg, Commented May 2, 2019 at 9:22
Because the first string isn't comparing as "greater" because 0 is less than 3, you're doing textual comparison, not date-based. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented May 2, 2019 at 9:54

Tim Stack · Accepted Answer · 2019-05-02 09:48:19Z

2

Instead of comparing strings, use the DateValue or DateSerial function.

DateValue

MsgBox (DateValue("07-12-2018") > DateValue("31-12-2016"))

This will correctly display True

Note:

DateValue recognizes the order for month, day, and year according to the Short Date format that you specified for your system

DateSerial

Is independent of the local system's Short Date format, since it uses separate arguments for the year, month, and date:

MsgBox (DateSerial(2018,12,7) > DateSerial(2016,12,31))

edited May 2, 2019 at 9:48

answered May 2, 2019 at 9:23

Tim Stack

3,2483 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Pᴇʜ Over a year ago

Or even better use DateSerial(2018, 12, 7) > DateSerial(2016, 12, 31) to make it independend from the operating system localization. DateValue needs the operating system date format to be dd-mm-yyyy to work with these string dates. If you use your code and the operating system is set to yyyy-mm-dd for example it will fail.

Tim Stack Over a year ago

Good point, I'll add that to the post. A downside of using DateSerial is that you can't use a complete date string, it will have to be broken up.

Pᴇʜ Over a year ago

You can never use a full string to convert it into a date (if you want it to be reliable result) because a string is lacking the information which part of the string the year, month or day is. Eg How can the computer know if 07-12-2018 is dd-mm-yyyy or mm-dd-yyyy? Computers cannot know it so that's the task of the programmer. You will always need to split the string into day, month, and year and use DateSerial if you not want Excel to start guessing.

Tim Stack Over a year ago

True to an extent. I can imagine a UF with a textbox that would only allow values in a certain format. But then the programmer would be better off declaring the value as a date in another variable.

Pᴇʜ Over a year ago

But the format that the textbox accepts must not be a "certain" format it musst be exactly "the" format that is used in the operating system. So if the workbook is used in more than one place (globalization) then this will still be tricky.

|

Collectives™ on Stack Overflow

Issue while comparing dates in VBA?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related