If statement based on time ranges

  • Thread starter Thread starter Dalena
  • Start date Start date
D

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!
 
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))))
 
Dalena said:
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))))
Click to expand...

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