How to convert and compare a date string to a date in Excel

Question

= "7/29/2011 12:58:00 PM" > NOW()

I'd like this expression to return FALSE and yet it returns TRUE.

I know I can break apart my datetime into a date and a time and add them together as follows:

= DateValue("7/29/2011") + TimeValue("12:58:00 PM") > NOW()

But, this seems inelegant to me. I want a simple function or approach that looks nice and I feel certain that it's out there but I just can't find it.

I also know there is a VBA function called CDate which can typecast the string into a datetime and that would be perfect. But, I don't see how to call a VBA function in an excel cell.

Answer 1

Multiply the string by one and the comparison function will work:

= 1*"7/29/2011 12:58:00 PM" > NOW()

The answer to your question is tightly related to @Jean-François's comment: Why is the date being interpreted by Excel as a Text and not by a date?

Once you find it out, you'll be able to do the comparison.

If that's because the string is being retrieved as a text, you can simply multiply it by one and the comparison function will work, then. But it applies only in case the string format is a valid date/time format in your regional settings.