If statement based on time ranges

[Dalena]

I am trying to ask that a percentage is returned whether a time
entered into cell B10 falls within one of four blocks of time. It is
working for the first three time ranges, but the last one is giving me
zero for all times entered in that range (6pm - midnight).

Here is my almost working formula:

=IF(AND(B10>TIME(24,0,0),B10<=TIME(6,0,1)),
100%,IF(AND(B10>TIME(6,0,1),B10<TIME(12,0,1)),
75%,IF(AND(B10>=TIME(12,0,0),B10<TIME(18,0,1)),
50%,IF(AND(B10>TIME(18,0,0),B10<TIME(24,0,1)),25%,0))))

I think the problem is my 'false' entry, but I don't know what should
be there. Can you help?

Thank you!

 

[isabelle]

hi,

=IF(AND(B10>TIME(0,0,0),B10<=TIME(6,0,0)),100%,
IF(AND(B10>TIME(6,0,1),B10<TIME(12,0,0)),75%,
IF(AND(B10>=TIME(12,0,1),B10<TIME(18,0,0)),50%,
IF(AND(B10>TIME(18,0,1),B10<TIME(23,59,59)),25%,0))))

 

[joeu2004]

It is working for the first three time ranges, but
the last one is giving me zero for all times entered
in that range (6pm - midnight).
Here is my almost working formula:
=IF(AND(B10>TIME(24,0,0),B10<=TIME(6,0,1)),
100%,IF(AND(B10>TIME(6,0,1),B10<TIME(12,0,1)),
75%,IF(AND(B10>=TIME(12,0,0),B10<TIME(18,0,1)),
50%,IF(AND(B10>TIME(18,0,0),B10<TIME(24,0,1)),25%,0))))

There is no time 24:0:0. Midnight is 0:0:0. So the following is
sufficient:

=IF(B10<=TIME(6,0,1),100%,
IF(B10<TIME(12,0,1),75%,
IF(B10<TIME(18,0,1),50%,25%)))

Note that in the second IF expression, we take advantage of the fact that we
failed the first test. So we know that B10>TIME(6,0,1) is true; we do not
need to test it.

Similarly in the third IF expression.