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

= "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 functio开发者_C百科n 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.

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.

You could wrap the VBA call in a custom function:

Function ReturnDate(ByVal datestr As String) As Date
    ReturnDate = CDate(datestr)
End Function

which you can use just like a formula in your sheet.

I'm upgrading the following from a comment to an answer:

Unless you have a very specific reason to do so (and right now I can't think of any), dates (and other values) really shouldn't be "hard-coded" in cells as strings like you show. Hard-coding the string like that makes it invisible and inflexible. The user will just see TRUE or FALSE with no indication of what this means.

I would just put your date 7/29/2011 12:58:00 PM in a cell on its own e.g. A1, and set the cell's format to some date format. Then you can say = A1 > NOW().

Contrary to @jonsca's and @Tiago Cardoso's answers, this answer doesn't address your specific question, but then again, what you are asking seems like really bad practice to me!

The simplest way to do this is to make a VBA function that uses CDATE and return your comparison. Then, call the function from an excel cell.

The VBA Function

Public Function compareDate(ByVal inputDate As String) As Boolean
  compareDate = CDate(inputDate) > Now()
End Function

Then in your spreadsheet, just do

=compareDate("YOUR DATE")

The function will return "FALSE" if it is older and "TRUE" if it is newer than Now()