Solved: Combine Formula

[jammer6_9]

I was having troubloe combining formula to read 2 separate criteria.

[Bob Phillips]

Use

=--(OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7)))

but ...

Why are you using a formula to determine whether the condition is true and transforming that to 1 or 0, then using CF on a value of 1, why not just use

=OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7))

as your CF formula?

[jammer6_9]

Actually I have spent a lots of time twisting the formula and I have ended up to that until I finally decided to call for a help ... Your suggestion works perfectly by modifying a little bit as you gave the idea.

=OR(AND(I$7>=$D8,($D8+$E8*(1/24))>I$7),AND(I$7>=$G8,($G8+$H8*(1/24))>I$7))

But still seems to have a problem as attached. Defined work hours was 4 but showing on the shaded as 5 hours.

Use

=--(OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7)))

but ...

Why are you using a formula to determine whether the condition is true and transforming that to 1 or 0, then using CF on a value of 1, why not just use

=OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7))

as your CF formula?

[Bob Phillips]

I think your problem is in the floating point processor, dividing the number of hours by 24 is just not accurate in all cases (not to mention that odd practice of multiply by 1 and divide by 24, *1/24, this is wasteful, /24 is the same). Anyway, rather than divide the hours by 24, changed it to multiply the times by 24 which means that it is all integer arithmetic.

=OR(AND(I$7>=$D8,($D8*24+$E8)>I$7*24),AND(I$7>=$G8,($G8*24+$H8)>I$7*24))

You seem to have missed my point of CF formula. I meant put the formula in the CF directly, not in the cell at all.

[jammer6_9]

Yeah I missed it... Thanks once again xld ... Its amazing. I never did that. I never have thought that I can put the formula in the CF not in the cell where it will be like a magic to the user

You seem to have missed my point of CF formula. I meant put the formula in the CF directly, not in the cell at all.

[Bob Phillips]

That's great, your problem is solved and your own skills-base has moved up a notch. Double whammy

[PatrickM]

Hey! I wonder why on my PC nothing works. So after reading this post and the conversations I did all the settings again but nothing works.

[jammer6_9]

Hi Patrick, instead of putting the formula below directly in the cell
=OR(AND(I$7>=$D8,($D8*24+$E8)>I$7*24),AND(I$7>=$G8,($G8*24+$H8)>I$7*24))

put instead in CF - Conditional Formatting

Hey! I wonder why on my PC nothing works. So after reading this post and the conversations I did all the settings again but nothing works.