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
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
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 (or even DAX if that's easier)
Thanks for your help in advance!
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 (or even DAX if that's easier)
Thanks for your help in advance!
