Using time values with an IF

[Col]

Hi all,

Is it possible to use hours and minutes (hh:mm) as the parameters in an IF
statement.

Regarding the formula below;

=IF(ISBLANK(E20),"",IF(E13<9,"Rest",IF(E13<11,1,"OK")))

The values in cell E13 is in hh:mm format, and as such the formula doesn't
compute.

I've tried changing the number 9 and 11 in the example above to

09:00

00:09:00

#09:00#

"09:00"

Without any success.

Can anyone please help me out with the correct syntax or suggest an
alternative - note I cannot change the cell format from hh:mm

Thank you,

Colin.

 

[T. Valko]

Try this:

=IF(E20="","",IF(HOUR(E13)<9,"Rest",IF(HOUR(E13)<11,1,"OK")))

 

[JE McGimpsey]

Times in XL are stored as fractional days, so the stored value in the
cell displaying

09:00

is 9/24 or 0.375, which you can see if you change the number format to
General.

So

=IF(ISBLANK(E20),"",IF(E13<9/24,"Rest",IF(E13<11/24,1,"OK")))

should work for you.

 

[Pete_UK]

You need to realise that times are stored internally by Excel as
fractions of a 24-hour day, so 6:00 is stored as 0.25, 18:00 as 0.75
etc. Thus you can't just refer to 9 directly in your formula if you
mean 9 hours.

You could do it like this:

=IF(ISBLANK(E20),"",IF(E13<9/24,"Rest",IF(E13<11/24,1,"OK")))

if the 9 and 11 both refer to hours. You could also use the TIME
function, like this:

=IF(ISBLANK(E20),"",IF(E13<TIME(9,0,0),"Rest",IF(E13<TIME(11,0,0),
1,"OK")))

which is a bit easier to understand.

Hope this helps.

Pete

 

[Col]

Thank you very much everyone for your replies.

Regards,

Colin.