Overlapping times and calculating actual hours worked

[excelmonk]

Hi All,

Here is my scenario. I have a list of employees and the time ranges of when they have worked on different appointments. This data comes in every day and I would consolidate files for each day using power query. The sample data is as follows

Employee	Scheduled_Start	Scheduled_End
ABC	4/14/2020 9:00	4/14/2020 16:00
DEF	4/14/2020 9:00	4/14/2020 16:00
DEF	4/14/2020 11:00	4/14/2020 14:00
ABC	4/14/2020 15:00	4/14/2020 17:00
ABC	4/14/2020 16:00	4/14/2020 18:30

I'm trying to calculate how many hours an employee worked on a particular days excluding the overlap times.

So for example employee ABC has three entries and all of them have some overlap. And same is the case with employee DEF. I'm trying to find a solution where I can define New Start Time where the new start time gets rid of the overlap problem.

For example

Employee	Scheduled_Start	Scheduled_End	New Start Time	New End Time	Hours Worked
ABC	4/14/2020 9:00	4/14/2020 16:00	4/14/2020 9:00	4/14/2020 16:00	7
DEF	4/14/2020 9:00	4/14/2020 16:00	4/14/2020 9:00	4/14/2020 16:00	7
DEF	4/14/2020 11:00	4/14/2020 14:00	4/14/2020 16:00	4/14/2020 14:00	0
ABC	4/14/2020 15:00	4/14/2020 17:00	4/14/2020 16:00	4/14/2020 17:00	1
ABC	4/14/2020 16:00	4/14/2020 18:30	4/14/2020 17:00	4/14/2020 18:30	1.5

For employee ABC for row 4 New start time is End time for ABC in row 1 and same way new start time in row 5 is same as end time in row 4. I follow the same logic for DEF.
To calculate working hours I simply check with if condition whether start time > End Time. And if start time is greater than end time I just make it 0. For example second row for DEF.

I would have multiple rows with different employees in my data. Is there an excel formula to create the column New Start Time. I'm open to use Power Query as well as I'm currently using that to consolidate data every day

Thanks for your help in advance!

 

[alansidman]

Duplicate post

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Overlapping times and calculating actual hours worked
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[excelmonk]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Overlapping times and calculating actual hours worked
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks for the information. I will surely keep this in mind for future.

 

[Peter_SSs]

I will surely keep this in mind for future.

Thanks for that confirmation.