Calculate time difference based on working hours

[stanlyj]

Cell A1 has the start Date and time
Cell B1 has the End date and time
Cell C1 has the person name: A or B or C
A's working hours is 8 am to 5 pm, B's working hours is 11 am to 8 pm, C's working hours is 5 pm to 2 am

In cell A2, i need to calculate the time difference between A1 and B1 (B1-A1) considering the person's name in cell C1 and their working hours.

Example:
A1= Start date and time: 3/30/2017 6 PM
B1= End date and time: 3/31/2017 1:30 PM
if C1 = person B
the time difference in A2 should be: 4:30 hours (since B's working hours is 11 am to 8 pm)
and vice versa..

[JeteMc]

Hello stanlyj and Welcome to Excel Forum.
The following proposed solution is dependent on a helper range which is located in D1:I3 of the attached file, but which could be moved to an out of the way part of the spreadsheet and/or hidden for aesthetic purposes.
In the helper range the following formulas are used to calculate time worked from Start of shift to End of Job and End of Shift to Start of Job respectively:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The SUM function then populates A2. Solution was tested with values for A, B and C given the start and end dates given, however I would suggest a more rigorous testing.
Let us know if you have any questions.

[stanlyj]

Hi JeteMc, Thank you for your reply. I've attached the excel sheet with my requirement. can u add formula for that..
I've drop down list of person's name in column D, and in column C i need to get the working hours details automatically based on the name i select from the drop down list.
Their shift timings are mentioned in columns G to I.
Attachment 510031

[JeteMc]

This proposed solution employs two helper columns which could be moved to an out of the way portion of the worksheet, such as columns XFC:XFD, and/or hidden for aesthetic purposes. The formulas for the two helper columns respectively are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

A SUM based formula populates column C, working hours.
Let us know if you have any questions.