Forum Discussion
Date Time - matching criteria and summing time
Hi guys,
Hoping to get some help on how to work out the following:
I have 2 tables:
Table A - Consists of where employees are meant to be working (facility) and the start datetime and end datetime.
Table B - When employees are unavailable, start datetime and end datetime.
I want to work out the following:
Using Table B, I would like to know the amount of time unavailable and using Table A, the facility they are meant to be working in and how long they are unavailable for, for that particular facility.
Results table that I am looking for
Schedule | |||||
Employee | Facility | Start DateTime | End DateTime | Unavailable Time | Unavailable Reason |
Dave | SE London | 10/01/2024 08:00 | 10/01/2024 12:00 | 2:00 | Sick Leave |
Dave | S London | 10/01/2024 12:00 | 10/01/2024 16:30 | 3:00 | Sick Leave |
Notice that the way that the unavailable time is calculated is based on the start and end time of employee for the particular Facility. Table A where the Employee is working in SE London, they are meant to be working from 8am-12pm, Table B where it shows they are unavailable between 10am-3pm, I am only summing the time up until 12:00pm.
Where for S London, start time is at 12pm and end time is 4:30pm (table A), the calculation I would imagine should take into account start time of S london 12pm, rather than the start time of the unavailability (which we know is 10am) which is before the start time of this employee for this particular facility.
Hope this makes sense - super happy to explain further as it looks a bit complex for me!
Please see below tables
Thank you so much!!!
Table A:
Schedule | |||
Employee | Facility | Start DateTime | End DateTime |
Dave | SE London | 10/01/2024 08:00 | 10/01/2024 12:00 |
Dave | S London | 10/01/2024 12:00 | 10/01/2024 16:30 |
Table B:
Unavailability | |||
Employee | Unavailable Reason | Start DateTime | End DateTime |
Dave | Sick Leave | 10/01/2024 10:00 | 10/01/2024 15:00 |
6 Replies
- OliverScheurichGold Contributor
In the demo file is a suggestion for 3 employees. The formula works with VLOOKUP and can easily be adapted for hundreds of employees.
- threw000Copper Contributor
OliverScheurich hey thanks so much for your help! This works however there is some further complex logic that isnt picked up correctly 😞 I.e. if the same person works a split shift say 08:00-12:00 in SE and 12:00-16:30 in S.
They take sick leave 1 hour during SE shift and 1 during S shift. Using the excel you provided, this isnt picked up correctly 😞
- OliverScheurichGold Contributor
You are welcome. Attached a suggestion for the new scenario where the same person has multiple unavailability entries.
- ExcelonlineadvisorIron Contributorcan you re-explain ? you can dm also
- threw000Copper ContributorHey - I will DM now - thanks for your help 🙂