If statement based on time ranges


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!
 
Ad

Advertisements

I

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))))
 
Ad

Advertisements

J

joeu2004

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))))

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top