CODEWITHSUNDEEP
excelvba
Gidon Wise
Gidon Wise

Reputation: 1916

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 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.

Upvotes: 4

Views: 20619

Answers (4)

Jean-François Corbett
Jean-François Corbett

Reputation: 38551

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!

Upvotes: 4

Tiago Cardoso
Tiago Cardoso

Reputation: 2107

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.

Upvotes: 8

Chris Flynn
Chris Flynn

Reputation: 953

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()

Upvotes: 3

jonsca
jonsca

Reputation: 10381

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.

Upvotes: 5

Related Questions