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.

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.