Join dates with continuity

[Eddistoff]

Good day everyone,

I am new using this great tool and also I am new in the community. I have been struggling with getting the information I required based on the continuity of dates, which is the following.

I need to identify the people who had continuity with leave of absence during a fiscal year and get the sum of total days, for example if a person was out from January 2nd and gets back on February 1st, then leaves again on Feb 2nd and gets back Feb 15th. So this case the person had a continued leave of absence and total days were 45 days. Another example would be a person who doesn't show some days to work for vacations or short leaves of absence, but during the month this person worked most of the time. 

Below a graphic example of the case:

ID worker	Worker's name	Sort of Leave Of Absence	Start Date (DD-MM-YYYY)	End Date (DD-MM-YYYY)
11111111	Jane Doe	Maternity leave	01-01-2019	04-01-2019
11111111	Jane Doe	Maternity leave	05-01-2019	24-01-2019	Continuos LOA
11111111	Jane Doe	Maternity leave	25-01-2019	30-01-2019
11111111	Jane Doe	Maternity leave	31-01-2019	01-03-2019
22222222	John Doe	Vacations	01-01-2019	15-01-2019
22222222	John Doe	Vacations	01-02-2019	05-02-2019
22222222	John Doe	Medical Leave	07-02-2019	10-02-2019	Not continuos LOA
22222222	John Doe	Vacations	21-02-2019	22-02-2019
22222222	John Doe	Medical Leave	03-03-2019	04-03-2019

I would be really gratefull if anyone can help to solve this.

Regards

Edgardo

[RolandSchubert · Accepted solution]

Hi @Eddistoff ,

DateTime functions and the Generate Rows tool are your friends here ... using the Generate Rows tool you create one row for each date in the date ranges, count the number of dates and you are done. You can easily add a fuctionality to include only workdays if needed.

A sample workflow is attached.

Let me know if thisworks for you. 

Best,

Roland

[Eddistoff]

Can't belive it was so simple and at the time complex 🙂

Thanks a lot RolandSchubert! I really appreciate your help!!!!!