5

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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