Calculate Shift Hours (Date/Time difference in an interval)

seanpark22

New Member
Hi,

We have a situation where we need to find "number of hours" spent by each employee on "a particular date" but in between "a fixed interval".

Fixed Interval - Daily from 7:30 AM to 4:30 PM
We have list of employees and dates for the data.
Shifts can spillover several days, so solution should let us know that employee's hours on each date in fixed interval (below line 1)
An employee can have several shifts in a day i.e. 4 shifts for 2 hours each (below line 3 and 4).

 Employee Start Date/Time End Date/Time X1 7/1/2020 6:00:01 AM 7/4/2020 2:00:00 AM X2 6/6/2020 8:35:09 AM 6/6/2020 3:35:09 PM X3 5/3/2020 6:30:00 AM 5/3/2020 7:50:00 AM X3 5/3/2020 8:30:00 AM 5/3/2020 2:30:00 PM

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Replies
4
Views
219
Replies
19
Views
313
Replies
1
Views
74
Replies
2
Views
43
Replies
1
Views
56