# Date Time - matching criteria and summing time

Copper Contributor

# 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!

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

# Re: Date Time - matching criteria and summing time

can you re-explain ? you can dm also

# Re: Date Time - matching criteria and summing time

In the demo file is a suggestion for 3 employees. The formula works with VLOOKUP and can easily be adapted for hundreds of employees.

# Re: Date Time - matching criteria and summing time

@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 😞

# Re: Date Time - matching criteria and summing time

Hey - I will DM now - thanks for your help 🙂

# Re: Date Time - matching criteria and summing time

You are welcome. Attached a suggestion for the new scenario where the same person has multiple unavailability entries.

# Re: Date Time - matching criteria and summing time

Thanks again! I will test and let you know how I go 🙂