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

[Cherub7210]

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

[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!

[Qiu · Accepted solution]

@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.

[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.

---