If statement using time

[j9mcl]

Hi

Can anyone help me with this IF statement?

In cell I3 I have summed hours from a range of cells (this has been formatted [h]mm )

In cell J3 I want it to say Y if the hours are greater than 20 in I3
In cell K3 I want it to say Y if the hours are greater than 50 in I3

I have used the statement =IF($I3>=TIMEVALUE("20:00"),"Y",""), in cell J3 which seems to work but when I use the statement =IF($I$3>=TIMEVALUE("50:00"),"Y","") in K3, it isn't working.

[CK76]

Timevalue cannot exceed 23:59:59. Any value greater will spill over into next day and gets truncated.

You can simply use... 20/24, 50/24 instead.
In Excel, 1 = 1 day = 24 hours. So Hour Value / 24 gives time value of hour.

Ex: =IF($I$3>=50/24,"Y","")

[Sunny18pc]

HI, Try the below:

Please Login or Register  to view this content.

How I reached to 0.8333, simple just convert the time to number, and use the equivalent number in formula.

[j9mcl]

Brilliant - thank you both so much.

[joeu2004]

Brilliant - thank you both so much.

But I hope you recognized CK76's response as correct and Sunny18pc's response as incorrect.

In general, it is unwise to replace an exact calculation like 20/24 and 50/24 with their decimal equivalents (e.g. 0.833333333333333 for 20/54).

But 0.8333, in particular, is a poor estimate of 20/24. In fact, if you format 0.8333 as [h]:mm, you will see 19:59 instead of 20:00, because 0.8333 is really about 19:59:57.120, and Excel does not round to the minute when the format is [h]:mm .

Moreover, of course, Sunny18pc's response does not directly address the critical part of your question, namely how to handle 50 hours. Following his paradigm, you would use I3 >= 2.08333333333333.

But again, CK76's paradigm -- I3 >= 50/24 -- is more correct.