Formula to compare time values

Question

Below excel formula is working fine but in some cases its not give me proper value .

Input:

19:20:42
24:58:36
26:11:18

After using this formula:

 =IF(TIMEVALUE(K7)>TIMEVALUE("09:00:00"),TRUE,FALSE)

I got the below output:

FALSE
TRUE
TRUE

What I Observe if the time value is > or = 24:00:00 it will not give me the proper answer. How do I fix this?

Answer 1

As an alternative to Captain's excellent answer, you could also use:

=IF(K7>(9/24),TRUE,FALSE)

DateTime values are internally stored as a number of days since 1/1/1900,
therefore 1 = 1 day = 24 hours. So 9/24 = 0.375 = 9 hours :-)

You can easily verify this by clearing the format of your DateTime cells.

---

Edit: note that such Boolean formula can be expressed in a shorter way without losing legibility:

=K7>(9/24)

Answer 2

When you go over 24 hours, Excel counts it as the next day... and then the TIMEVALUE is the time the next day (i.e. 00:58:36 and 02:11:18 in your examples) and can, therefore, be before 0900. You could do DATEVALUE(K7)+TIMEVALUE(K7) to ensure that you count the day part too...