Using time values with an IF

  • Thread starter Thread starter Col
  • Start date Start date
C

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.
 
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.
 
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
 
Back
Top