Hours between two dates and 2 shifts

Question

I need to calculate total hours between two dates, considering two shifts: 06:30-16:30 and 18:30-04:30, from Monday to Friday, excluding weekends.

start date with time start date with time Overtime Shift 1(06:30:00-16:30:00) Shift 2(18:30-04-30) 20-11-2023 06:30:00 24-11-2023 15:00:00 00:00:00

How to make a formula that calculates the Total hrs during the period for shift 1 and 2 based on input from start date/end date with time + overtime? thanks.

I have tried many different formulas however, nothing has worked.

Answer 1

1. separate date and time for both start and end fields(if your data is very big do it with vb scripting)
2. formula Start_date + End_date + End_time - Start_time + Over_time then format the cell to time format

## E1 cell
=A1+C1+D1-B1+E1