Lunch break formula help

[JoSmith]

I have the below formula correctly which deducts 30mins lunch break from work times

=IF(COUNT(C23:D26)=0,"",SUM(D23-C23+D24-C24+D25-C25+D26-C26))-"0:30"+0

How do I amend this to only deduct the 30 mins if they work longer than 5 hours. My guys sometimes only work 4 hours but then work longer days other days.

Thanks in advance!

 

[theBardd]

Try

=IF(COUNT(C23:D26)=0,"",SUM(D23:D26,-C23:C26)-(SUM(D23:D26,-C23:C26)>--"05:00")*1/48)

 

[JoSmith]

Try

=IF(COUNT(C23:D26)=0,"",SUM(D23:D26,-C23:C26)-(SUM(D23:D26,-C23:C26)>--"05:00")*1/48)

This works for most cells except for the one that I have time commencing at 00:01 (12:01am)

 

[theBardd]

Wrong in what way? 13:59 looks correct to me.

 

[JoSmith]

Hi. Sorry it was right. I was using the wrong times.

I am having a different issue now.

I need to be able to use the 4 rows for rostered / worked times separately as there are occasions that my officers do a split shift and I can't seem to get the formula to accommodate this.

Thanks in advance

 

[theBardd]

Can yu gian example of what you mean that? What the current formula does, what you now want.

 

[JoSmith]

Can yu gian example of what you mean that? What the current formula does, what you now want.

Sorry was not explaining myself properly.

The current formula I have is this: =IF(COUNT(C11:D14)=0,"",SUM(D11:D14,-C11:C14)-(SUM(D11:D14,-C11:C14)>--"05:00")*1/48)

It works if the guys are just doing one shift which was the original plan. But I have been told that the officers can work multiple shifts i.e. a morning shift and an afternoon shift to make up the 7-8 hrs. The current formula is not recognizing the two or more shifts. For example the 0:01-5:01 shift is 5 hrs and the 10:00-12:00 is 2 hours so it should be saying 7 hours not 16:30

It has me stumped.

 

[JoSmith]

Hi all,

Still needing help with a solution....

TIA