Find available time between End and Start Dates

[DomMiro]

Not sure if this is doable but i would like to look at all the star and end date highlighted in the picture below and look for differences between each End and Start date to see when a person is available an not assigned to any projects. I would like the date range to be between 01/01/2019 and 09/30/2019 but the range will be expanded or changed as time goes by.

For example if a person is assigned to a project with the first set of start and end dates is 01/15/2019-02/01/2019 and second set is 02/15/2019-02/20/2019, the available date range would be 01/01/2019-01/14/2019, 02/02/2019-02/14/2019, 02/21/2019-09/30/2019.

The first picture has multiple records for each auditor but in he output i would like to have only one record for each as shown in the 2nd picture.

I am attaching a sample file as well.

Thank you in advance for any help or tips.

[MichalM]

@DomMiro 

Generate Rows tool is your friend here. You can use it to generate every available date between two dates, use the join tool to match it against the period you're looking for and isolate out the booked out dates. I've attached a sample of how this could be used.

[wdavis]

Hi @DomMiro 

I have looked at this in a similar way to Michal, but attempting to replicate the table you have in your after tab. By summarizing for each auditor their minimum values for each period, you can then Transpose and Cross-Tab your data back together to have only one line for each auditor and just displaying the dates they are available. The formula tool is then finding the date before or after your current dates they are busy

The only thing to note in this process is that you will be using the Select tool to re-order and rename your fields into the order and naming conventions you are after. This is because for example where we have "End1" in the original dataset, this needs to now be the "Start1" Field. 

Similarly, in the final Formula tool, you would need to manually enter the start date for your period. This is just a case of amending the Formula field

[jrgo]

Hi @DomMiro 

Attached is a solution that produces the output you specified.

I'm sure that there are other variables that may require some adjustments, but this should give you a good starting point to work with.

Hope this helps!

Jimmy
Teknion Data Solutions

[estherb47 · Accepted solution]

Hi @DomMiro 

I also started down the generate dates and find the unmatched, but that wasn't getting the result.

You also have to account for overlapping dates, and dates before the booked time starts.

The attached workflow is a bit convoluted, but gets the job done.

Let me know if this helps. I'll noodle on it some more and see if I can solve it more elegantly.

Cheers!

Esther

[DomMiro]

Thanks everyone for your help and sorry for the late reply. EstherB47 workflow has given me exactly what I needed. The other two were close and they have me ideas. Thanks again!

[eisesara]

Hi all, I need to do something very similar. I believe I need to use a multi-row formula tool and group by node, data, associate, start time, endtime, and duration and then I need new rows for all hours the associate in between the start time and endtime starting from "starttime" ending with "endtime".

I also need to generate another column grouped by node and date and start time to give each unique associate in that node, date, and hour, a number starting with 1.

Result should look like this.