Trouble comparing 2 separate Time cells

[JMP]

I'm trying to compare 2 time (h:mm) cells which pose a range (a core work hours) with 8 other cells which are (Hours Worked IN, Hours Worked OUT, LEAVE IN, and LEAVE OUT)X2 since each day may have a 2-line entry.

Example: CORE CELL HOURS 09:00($Y$4) 14:30($Z$4)
WORKIN WORK OUT LEAVE IN LEAVE OUT
Line1 08:00 12:00 12:00 13:00
Line 2 13:00 16:00

There may be some cells that are blank (if no leave is taken.) I don;t havce a Leave IN or OUT in the below formula. I want to make sure the core hours are covered inclusively with these possible 8 cells (I need an error code in the adjacent cell. Here's what I've got so far: =IF(OR(AND(WORKINLine1>0.01)*(WORKINLine1>$Y$4)),"C",IF(AND(LEAVE INLine1>0.01)*(LEAVE INLine1<$Y$4)*OR(L16>$Y$4),"C",IF(AND(WORKINLine2>0.01)*(WORKINLine2>$Y$4),"C",IF(AND(LLEAVE INLine2>0.01)*(LEAVE INLine2>$Y$4),"C",IF(AND(WORK OUTLine1>0.01)*(WORK OUTLine1<$Z$4),"C",IF(AND(LEAVE OUTLine1>0.01)*(LEAVE OUTLine1<$Z$4),"C ",IF(AND(WORK OUTLine2>0.01)*(WORK OUTLine2<$Z$4),"C"," "))))))) Please help!

 

[Andy B]

JMP

On the example you've given, what should the result be?

Andy.

I'm trying to compare 2 time (h:mm) cells which pose a range (a core work

hours) with 8 other cells which are (Hours Worked IN, Hours Worked OUT,
LEAVE IN, and LEAVE OUT)X2 since each day may have a 2-line entry.

Example: CORE CELL HOURS 09:00($Y$4) 14:30($Z$4)
WORKIN WORK OUT LEAVE IN LEAVE OUT
Line1 08:00 12:00 12:00 13:00
Line 2 13:00 16:00

There may be some cells that are blank (if no leave is taken.) I don;t

havce a Leave IN or OUT in the below formula. I want to make sure the core
hours are covered inclusively with these possible 8 cells (I need an error
code in the adjacent cell. Here's what I've got so far:
=IF(OR(AND(WORKINLine1>0.01)*(WORKINLine1>$Y$4)),"C",IF(AND(LEAVE
INLine1>0.01)*(LEAVE
INLine1<$Y$4)*OR(L16>$Y$4),"C",IF(AND(WORKINLine2>0.01)*(WORKINLine2>$Y$4),"
C",IF(AND(LLEAVE INLine2>0.01)*(LEAVE INLine2>$Y$4),"C",IF(AND(WORK
OUTLine1>0.01)*(WORK OUTLine1<$Z$4),"C",IF(AND(LEAVE OUTLine1>0.01)*(LEAVE
OUTLine1<$Z$4),"C ",IF(AND(WORK OUTLine2>0.01)*(WORK OUTLine2<$Z$4),"C","
"))))))) Please help!

 

[Mark Graesser]

JMP
It looks like you have some structure problems with your OR and AND statements. The items inside these functions should be seperated by commas (,) not astericz (*)

Your first IF statement has an OR function with only one arguement inside it

I think your second IF statement should be structured as
OR(AND((LEAVE INLine1>0.01),(LEAVE INLine1<$Y$4)),(L16>$Y$4)

In any case your formula has reached the maximum of 7 nested IF statements. This won't cause a problem, but usually there are other techniques to avoid these long and complicated IF statements

Could you describe what you mean by "make sure the core hours are covered inclusively with these possible 8 cells". If we have a better idea of what you need we might be able to provide another solution

Good Luck
Mark Graesse
(e-mail address removed)

----- JMP wrote: ----

I'm trying to compare 2 time (h:mm) cells which pose a range (a core work hours) with 8 other cells which are (Hours Worked IN, Hours Worked OUT, LEAVE IN, and LEAVE OUT)X2 since each day may have a 2-line entry

Example: CORE CELL HOURS 09:00($Y$4) 14:30($Z$4
WORKIN WORK OUT LEAVE IN LEAVE OU
Line1 08:00 12:00 12:00 13:0
Line 2 13:00 16:0

There may be some cells that are blank (if no leave is taken.) I don;t havce a Leave IN or OUT in the below formula. I want to make sure the core hours are covered inclusively with these possible 8 cells (I need an error code in the adjacent cell. Here's what I've got so far: =IF(OR(AND(WORKINLine1>0.01)*(WORKINLine1>$Y$4)),"C",IF(AND(LEAVE INLine1>0.01)*(LEAVE INLine1<$Y$4)*OR(L16>$Y$4),"C",IF(AND(WORKINLine2>0.01)*(WORKINLine2>$Y$4),"C",IF(AND(LLEAVE INLine2>0.01)*(LEAVE INLine2>$Y$4),"C",IF(AND(WORK OUTLine1>0.01)*(WORK OUTLine1<$Z$4),"C",IF(AND(LEAVE OUTLine1>0.01)*(LEAVE OUTLine1<$Z$4),"C ",IF(AND(WORK OUTLine2>0.01)*(WORK OUTLine2<$Z$4),"C"," "))))))) Please help!