Total Time Less Lunch Overlap

L

Legacy 436357

Guest
Hello is there a way to adjust the formulas in Column I so that if the times overlap the lunch break (12 pm to 12:30 pm) it deducts that time?

I have the Lunch In L12 and Lunch Out L13 if that matters.

So it should be a 30 minute break.

The result of the new formula would then be I5 2.5 hours.

Thank you

Excel Workbook
GHI
3Start TimeEnd TimeTask Time
48:00 AM10:00 AM2:00
510:00 AM1:00 PM3:00
Week 27
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi,

Cell I4 be =IF($H4<>"",IF(AND($G4<=$L$11,$H4>=$L$12),$H4-$G4+$L$11-$L$12,$H4-$G4),"")
 
Last edited:
L

Legacy 436357

Guest
Thank you again but I had to adjust the cells that contained the Lunch In and Lunch Out.

I do appreciate the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,822
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top