Identifying if the difference between two dates falls into a specific bucket

i would like to ask your assistance on how can i Identifying if the difference between two dates falls into a specific bucket. We need to remove the holidays in the identification hope you can give recommendations

Created Date	Closed Date	Time bucket
6/28/2024 22:23	6/28/2024 22:26	<24 hrs
6/28/2024 22:20	6/28/2024 22:21	<24 hrs
6/25/2024 18:16	6/26/2024 19:37	24 to 48
6/24/2024 18:19	6/25/2024 21:11	24 to 48
6/12/2024 20:19	6/14/2024 20:21	48 to 72
10/3/2023 15:44	10/12/2023 12:32	>72 hrs
1/23/2024 18:23	1/29/2024 12:56	>72 hrs

Date Time

Accepted answers

Qiu

@Cherub7210 @alexnajm
I would like to share my take on this one.

The solution from @alexnajm is very nice, but I noticed that it is counting by Days, which may give incorrect answer for the case that, say case created on previous day night and closed on the morning of next day, it would be counted as 2 days but actually its bucket shall be lest than 24 hours.

I made some changes to the flow and assume that you guys dont work on Holidays 😂, menaing no cases will be created or closed during holidays.

0913-Cherub7210.yxzp

All comments

alexnajm

Do you have a list of holidays? That's a big determining factor - you'll need those listed out to be able to remove them from the analysis

Cherub7210

@alexnajm here are the dates of the holidays

1/1/2024

1/15/2024

2/19/2024

5/27/2024

6/19/2024

7/4/2024

9/2/2024

11/11/2024

11/28/2024

12/25/2024

alexnajm

That's good - but fair warning, you are now going to have to maintain a list every year for those holidays! See if you can find a source that updates often, or has them for several years

This is what I would do, and you can take it from here!

Identifying if the difference between two dates falls into a specific bucket.yxmd

Qiu

@Cherub7210 @alexnajm
I would like to share my take on this one.

I made some changes to the flow and assume that you guys dont work on Holidays 😂, menaing no cases will be created or closed during holidays.

0913-Cherub7210.yxzp

alexnajm

I agree @Qiu - I was purposefully being lazy and giving a workflow that worked for the prompt given I agree hours would be better but didn’t want to assume cutoffs and make it more complicated haha

Cherub7210

thank you both for your solution @Qiu and @alexnajm i will try this on a larger dataset and get back to you if i have questions

Cherub7210

@Qiu if i do want it down to minutes would i just convert the hours to minutes in datetime difference? in days loop should i just change 1 to number of minutes in a day?

Cherub7210

@Qiu do i also need to list the sat and sundays? or does it remove it automatically?

Qiu

@Cherub7210

if i do want it down to minutes would i just convert the hours to minutes in datetime difference? in days loop should i just change 1 to number of minutes in a day?
Yes, we need to convert the hours to minutes if you want it down to minutes. But I dont think we need to change 1 to number of minutes in a day for date generation.
do i also need to list the sat and sundays? or does it remove it automatically?

Only provided holiday is considered to be filtered out, the WeekEnd is accounted in the Difference.

You can remote them by a filter tool.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167