If statements for time format

I have 4:12 in cell C12 (time format h:mm) and 4:18:57 in cell C13 (time
format h:mm:ss),
I want to write a formula in cell D12 which would give me a score of 20 if
the value in C12 is less than or equal to 5:00 and 0 if the value is more
than 5:00.
Similarly I want to write a formula in D13 which would again give me a score
of 20 if the value in C13 is more than or equal to 8:00:00 and 0 if value is
less than 8:00:00

The trick is to change your time '5:00' in a formula to a time value

D12 formula =(C12<=TIMEVALUE("5:00"))*20

D13 formula =(C13>=TIMEVALUE("8:00"))*20

The formulae above work because the evalueation part of the equation returns
1 (TRUE) or 0 (FALSE)

You could also us and If statement:

=if(C12<=TIMEVALUE("5:00"),20,0)




"Shesh" <[email protected]> wrote in message
news:[email protected]...
>I have 4:12 in cell C12 (time format h:mm) and 4:18:57 in cell C13 (time
> format h:mm:ss),
> I want to write a formula in cell D12 which would give me a score of 20 if
> the value in C12 is less than or equal to 5:00 and 0 if the value is more
> than 5:00.
> Similarly I want to write a formula in D13 which would again give me a
> score
> of 20 if the value in C13 is more than or equal to 8:00:00 and 0 if value
> is
> less than 8:00:00