CF for time difference - fixing formula

Hi,

I have this CF formula for highlighting cells If time difference between working shifts are less than 11 hours :

Please Login or Register  to view this content.

This formula is inserted in Start/End cells and kind of works for night shifts, but problem is that It only calculates difference from cells.

So mistake is done when, example :

- on 1st Day I select End time like "14:00", and then next day I select "15:00" in Start cell ;

In this case It highlights cells, but It shouldn't.

Any way to fix this ??

Take a look at my sample, you'll understand better !!

Hi Pepe,
unfortunally I have a lot of VBA code for merged cells, so unmerging cells is not what I'm looking for. Have you any other ideas ?

Thanks for help though !!

Unmerge cells d5,d5,f5,g5 and place dates in e5 and f5 and try =AND($E$8<>""; $F$8<>"";( $F$8+$F$5-$E$8-$E$5)<11/24) as CF

Tried that too, but not working...

You should avoid using merged cells if at all possible - they cause nothing but problems with formulas

I realized that some time ago, but now I'm very close to finishing everything, so I would not like to start all over again

Originally Posted by FDibbins

OK try this for the CF rule...
=$D$5+$E8+11/24>=$F$5+$F8

This one works, but problems with night shift. If you for example select shift from 18-06 on 1st day, you shouldn't select start time of shift next day till 17:00, otherwise CF

You think that could be done ?

What about combining both formulas ? I was thinking - If D8 is more than 12:00 (all night jobs starts with more than 12:00) then first CF formula, otherwise second one.

Tried this but not working :

Please Login or Register  to view this content.

anybody have an idea how to combine them correctly ??

I almost solved this formula.

Only thing left to include is to highlight both cells in case like this, example :

- If first day shift is 18-06, and second day shift is 07-19.


my current formula is :

=IF($D$8<TIMEVALUE("12:00");$D$5+$E8+10/24>=$F$5+$F8;IF($D$8>TIMEVALUE("12:00");AND($E8<>""; $F8<>""; 10/24>=IF($E8>$F8;1;0)+$F8-$E8;10/24>=IF($E8<=$F8;1;0)+$F8-$E8);0))

Anybody have any suggestions ?

Does this modification of FDibbins formula work?

Formula:

Please Login or Register  to view this content.

No, this doesn't work. Thanks for trying

I think it may work if you include the dates in the calculation.

My conditional formatting formula:

Please Login or Register  to view this content.

Lewis

Hi LJMetzger,

tried this but still not working. Just as in mine formula, only one combination not working - for night shifts

If you select lets say 18:00 in D8 and 03:00 in E8 - that's night shift..... So in F8 any time that is less than 14:00 should CF both cells (E8 & F8).


I created formula for only that combination, but cannot combine It with other parts. I tried with nested IF, OR, AND - so far no success. But I'm sure this is possible

Formula for that combination :

Please Login or Register  to view this content.

or just

10/24>=F8-E8

Maybe you can combine this your formula ?

Lukael, you can change all those

Formula:

Please Login or Register  to view this content.

into number

Formula:

Please Login or Register  to view this content.

Hey Zbor,

That should do It

I'm not at home right now, so I can't test It, but I think everything is going to be just fine. I'll let you all know tomorrow

If someone has maybe another "easier" solution, please post It, I think this "between" time interval calculation is something to discuss about !