Join dates with continuity

Question

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 workerWorker's nameSort of Leave Of AbsenceStart Date (DD-MM-YYYY)End Date (DD-MM-YYYY)  11111111Jane DoeMaternity leave01-01-201904-01-2019

11111111Jane DoeMaternity leave05-01-201924-01-2019Continuos LOA11111111Jane DoeMaternity leave25-01-201930-01-2019 11111111Jane DoeMaternity leave31-01-201901-03-2019 22222222John DoeVacations01-01-201915-01-2019

22222222John DoeVacations01-02-201905-02-2019 22222222John DoeMedical Leave07-02-201910-02-2019Not continuos LOA22222222John DoeVacations21-02-201922-02-2019 22222222John DoeMedical Leave03-03-201904-03-2019

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

Regards

Edgardo

RolandSchubert · Accepted Answer

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