Time period between different dates in different rows

[Dario1]

Hello Guys, I need help to calculate the interval of days between two dates.

The status of one ticket should pass by stages and I need to know how many days was the ticket on each status.

Ticket	Submitted date	Worked on	Status
AA123	1/22/2019	1/31/2019 10:09	A
AA123	1/22/2019	2/1/2019 7:47	B
AA123	1/22/2019	2/8/2019 14:06	C
AA123	1/22/2019	2/26/2019 8:22	D
AA123	1/22/2019	3/4/2019 10:49	E
BB123	1/23/2019	2/8/2019 14:06	B
BB123	1/23/2019	2/10/2019 14:07	C
BB123	1/23/2019	2/11/2019 14:09	D
BB123	1/23/2019	2/12/2019 12:07	E

Example in the ticket AA123 row 1 has passed 9 days between the submitted date and the work day, the second status was mas 1 day after and the 3rd row 7 days.

Do you know how can I calculate this ?

Also if you could add a formula to calculate this only in business days will be much appreciated.

Thanks in advance.

[adamorse]

DateTimeDiff([firstdate],[laterdate],"days") will give you the number of days inbetween the first date and the later date. 

If you divide by seven you'll know how many weeks occurred, and rounding up and multiplying by two should give the number of weekends if I'm thinking this through right (assuming the dates are all occurring on business days).

[The_Dev_Kev_Env]

Hi Dario,

You'd want to use the "multi-row" formula here. 

You would sort the data by Ticket and Worked On date. Then in multi row tool, create new field called days passed as an int. The formula would look something like this :

iif(isnull([Row-1:worked_on]),0,datetimediff([worked_on],[Row-1:worked_on],'days') +[Row-1:days_passed])

Best,

devKev

[danilang · Accepted solution]

Hi @Dario1 

This ones a little tricky because for the first row in the Ticket, the elapsed is the difference between Submitted and Worked On and for the subsequent Status items, it the difference between the Worked on and the previous Worked on.  When you add on the fact that you're looking for business days and not calendar days, it becomes a multi-step process. 

This work flow gives you what you're looking for.  

It generates a row for each of the dates between the start and end and then removes the weekends and holidays(must be considered if you're counting business days).  Count the number of rows remaining and you have your answer.

Note that you may need to adjust the holiday list for your locale.

Dan

[Dario1]

Thanks for your help and time.
You have solved my problem.
Bests.