CF for time difference - fixing formula

[Lukael]

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

[Pepe Le Mokko]

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

[Lukael]

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

[Lukael]

What I meant,

any editing not desirable, just formula fix !

[Lukael]

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

[FDibbins]

I know this does not answer your question, but...

unfortunally I have a lot of VBA code for merged cells

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

[Lukael]

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

[FDibbins]

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

[Lukael]

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 ?

[Lukael]

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

[Lukael]

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 ?

[zbor]

Does this modification of FDibbins formula work?

Please Login or Register  to view this content.

[Lukael]

No, this doesn't work. Thanks for trying

[LJMetzger]

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

[Lukael]

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]

Ok, I'm really getting and headache now...

I think (but not sure), that this formula might work :

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

Yes, It's a long formula, I know. Hard to explain It, but I tried to nest all combinations with IF function - I went step to step till I ended this far. In some cases formula shows True or False, and in some cases 1 or 0, in correct results.

Problem ? Yes. This formula can't be pasted in CF.

Why ? Is It too long ??

Maybe someone could try to paste It ?

[Tony Valko]

This formula can't be pasted in CF.

Why ? Is It too long ??

Yes, it's too long.

CF formulas can not be more than 255 characters long.

Your formula is 292 characters.

[zbor]

Lukael, you can change all those

Please Login or Register  to view this content.

into number

Please Login or Register  to view this content.

[Lukael]

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 !

[Lukael]

Have tested It today, It works just fine !!

This is now final working formula :

Please Login or Register  to view this content.

Thank you all for your help, specially to FDibbins & zbor !!

[FDibbins]

Im really happy you finally got what you needed here, and thanks for the feedback