This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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