Using time values with an IF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Col - 24 Apr 2008 17:14 GMT

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.

Reply to this Message

T. Valko - 24 Apr 2008 17:27 GMT

Try this:

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

Signature

Biff
Microsoft Excel MVP

> Hi all,
>
[quoted text clipped - 26 lines]
>
> Colin.

Reply to this Message

JE McGimpsey - 24 Apr 2008 17:30 GMT

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.

> Hi all,
>
[quoted text clipped - 26 lines]
>
> Colin.

Reply to this Message

Pete_UK - 24 Apr 2008 17:32 GMT

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

> Hi all,
>
[quoted text clipped - 26 lines]
>
> Colin.

Reply to this Message

Col - 24 Apr 2008 18:38 GMT

Thank you very much everyone for your replies.

Regards,

> Hi all,
>
[quoted text clipped - 26 lines]
>
> Colin.

Reply to this Message