Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on time ranges
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(B10TIME(24,0,0),B10<=TIME(6,0,1)), 100%,IF(AND(B10TIME(6,0,1),B10<TIME(12,0,1)), 75%,IF(AND(B10=TIME(12,0,0),B10<TIME(18,0,1)), 50%,IF(AND(B10TIME(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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on time ranges
hi,
=IF(AND(B10TIME(0,0,0),B10<=TIME(6,0,0)),100%, IF(AND(B10TIME(6,0,1),B10<TIME(12,0,0)),75%, IF(AND(B10=TIME(12,0,1),B10<TIME(18,0,0)),50%, IF(AND(B10TIME(18,0,1),B10<TIME(23,59,59)),25%,0) ))) -- isabelle Le 2012-05-02 19:06, Dalena a écrit : 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(B10TIME(24,0,0),B10<=TIME(6,0,1)), 100%,IF(AND(B10TIME(6,0,1),B10<TIME(12,0,1)), 75%,IF(AND(B10=TIME(12,0,0),B10<TIME(18,0,1)), 50%,IF(AND(B10TIME(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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on time ranges
"Dalena" wrote:
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(B10TIME(24,0,0),B10<=TIME(6,0,1)), 100%,IF(AND(B10TIME(6,0,1),B10<TIME(12,0,1)), 75%,IF(AND(B10=TIME(12,0,0),B10<TIME(18,0,1)), 50%,IF(AND(B10TIME(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 B10TIME(6,0,1) is true; we do not need to test it. Similarly in the third IF expression. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If then statement based on time of day | Excel Programming | |||
Multiple If Then Statement based on ranges | Excel Worksheet Functions | |||
IF statement for time based data | Excel Worksheet Functions | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
Using an IF statement on time based data | Excel Discussion (Misc queries) |