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

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

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).

Emp 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  Register To Reply

2. ## Re: Calculate Shift Hours (Date/Time difference in an interval)

Perhaps the following will help.
1. Add three columns to the raw data
The first column calculates the time on the first day using:
Formula:  `Please Login or Register  to view this content.`

The second column calculates the time on the last day using: =IF(OR(INT(B3)=INT(C3),MOD(C3,1)<=B\$1),0,MIN(MOD(C3,1)-B\$1,C\$1-B\$1))
The third column calculates the time for the days between using: =IF(INT(B3)=INT(C3),0,(INT(C3)-INT(B3)-1)*(C\$1-B\$1))
2. Results are reported in a pivot table where Emp is placed in the Row area and a calculated field is placed in the values area.
The formula for the calculated field is: ='First Day'+'Last Day' +Between
For future reference, you will usually get faster results if you upload a sample .xlsx file (instructions are in the banner at top of the page)
Let us know if you have any questions.  Register To Reply

3. ## Re: Calculate Shift Hours (Date/Time difference in an interval)

Thanks JeteMc. The formula just saved a lot of manual efforts at my end. Cheers.  Register To Reply

4. ## Re: Calculate Shift Hours (Date/Time difference in an interval)

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 