I want to check if a set of fields indicate times of the day that are before 11am. The dates are stored in this format: 8:42:00 AM

I tried A1<"11:00:00 AM" but it always returns True, even if it was after 11am

Maybe a solution is convert it to Epoch first? or is there a more straight forward way? any working approach will be appreciated.

2 Answers 2


The problem is that "11:00:00 AM" is not a time value, it's just a string with characters 1, 1, :, 0, and so forth. You can convert it to a time value with timevalue function:

=A1<timevalue("11:00:00 AM")

or better yet, define the time directly with time

 =A1<time(11, 0, 0)

The second approach is locale-independent; it does not rely on conventions such as AM/PM vs 24 hour clock.


I had an instance where the time based formula above didn't work properly, I don't know if it had to do with a custom format being applied to the time column. The stored value for that column appears to be a timestamp, as such I had to use the following formula:

=((A1-int(A1))<time(11, 0, 0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.