Formula to Calculate Clock Out Time

[Legacy 436357]

Hi,

Can someone please come up with a formula? If I clock in at 8 am (cell L5) and need to work 9 hours (cell O2) what time do I clock out?

There is a lunch break:

L2 (12 pm)
L3 (1 pm)

Also this may be a job that carries over from the previous day as in the 18.0 hour JOB TIME can the formula allow for this too?

Thank you

Excel Workbook
	I	J	K	L	M	N	O
2			Lunch Start	12:00 PM	Max working hours per day		9
3			Lunch End	1:00 PM	Max hours per Invoice		18
4	JOB TIME	START DATE	DAY	START TIME	END DATE	DAY2	END TIME
5	6.0	8/27/2019	,"dddd")]Tuesday	8:00 AM	<=$O$2,],WORKDAY(],1,Holidays))]8/27/2019	,"dddd")]Tuesday
6	18.0	8/30/2019	,"dddd")]Friday	8:00 AM	<=$O$2,],WORKDAY(],1,Holidays))]9/3/2019	,"dddd")]Tuesday
Appointments

 

[Legacy 436357]

Thanks I will figure out something.