Time Period Overlap

[scollier1993]

Hi,

I am trying to do a resource planning analysis that provides me with a flag/report whenever a resource is assigned to a project for more than 40 hours during a week.

The data looks like this: 

Resource	Project	Start Date	End Date	Weekly Hours
Tom	Client A	2/25/2023	5/10/2023	20
Tom	Client B	4/10/2023	6/9/2023	30
Alex	Client C	9/1/2023	3/1/2024	40
Alex	Client D	11/1/2023	2/15/2024	10

The workflow should first check if there is an overlap of the start-end date periods and if there is, check if the sum of weekly hours exceeds 40.

So in the two examples above, the outcome should be something like this:

"Between 4/10/2023 and 5/10/2023, Tom's weekly hours exceed 40". 
Because he is staffed on two clients during that timeframe and his weekly hours would be 50.

Similar for Alex:

"Between 11/1/2023 and 2/15/2024, Alex's weekly hours exceed 40".

I already have a workflow that compares the time periods and gives me a report when there is any overlap.

But I need help with the sum calculation of hours at any point during these time periods, because if the total hours are less than 40, the overlap is OK and does not need to be raised.

Thank you!

[binuacs]

@scollier1993 Something like below?

[oyes2704]

Hi @scollier1993

You were really close! I added the number of hours as summary on overlaps..

However in your flow if they had 2 or more project overlaps that might not work. You would need to add the "accidents" of the overlaps at least. I created a stab for you here. Also, if the overlapping projects would be back to back, you probably want to enhance it even further. Let me know if you need more help. 

Best,

Oly