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. TicketSubmitted dateWorked onStatusAA1231/22/20191/31/2019 10:09AAA1231/22/20192/1/2019 7:47BAA1231/22/20192/8/2019 14:06CAA1231/22/20192/26/2019 8:22DAA1231/22/20193/4/2019 10:49EBB1231/23/20192/8/2019 14:06BBB1231/23/20192/10/2019 14:07CBB1231/23/20192/11/2019 14:09DBB1231/23/20192/12/2019 12:07E 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.

Time period between different dates in different rows

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.

Workflow

Accepted answers

danilang

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

ElapsedDays.yxmd

All comments

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

Hi @Dario1

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

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

Dan

ElapsedDays.yxmd

Dario1

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703