True days in past

[Madzy]

I want to get the true business value of something that has been on hold.

For example today is August 28 2023 and something has been on hold for 54 days but those days includes weekend and holidays and we don't want that. We want that # of business days its been on hold.

08/28/2023 -------->   54 days --------> 07/05/2023

                     How many of those days

                         were business days

I was thinking of

1. joining with a standard reference file that we have that has date to fiscal
2. join it based on the original date
3. then remove the ones that say weekend & holidays 
4. Then sum it based on today vs original date 

If anyone has any other better ideas please let me know!

[JosephSerpis]

Hi @Madzy have a look at this weekly challenge which asked this exact question and has a number of solutions from users who attempted to solve this challenge.

[Madzy]

But the dates are not supposed to be chosen by a user. In an excel file I have the original date and then the reporting date and the days in hold.  I don't want it as a Macro.

[Prometheus]

@Madzy Check out this post. It may be helpful to you: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Business-Day-formula/td-p/4845... 

[JosephSerpis]

Hi @Madzy you can take the workings of the macro to fit your challenge the underlying workflow could be used therefore all you need to do is copy the solution and remove the interface tools and replace the Text Input with your excel file with the dates to fit your specfic ask as an example. You may have to convert your dates into ISO format (YYYY-mm-dd).

[Madzy]

This is just an example, but I have 21,000 records just like this and I want to know how many business (No Weekends and no Holidays) days between them because the days in hold does include weekends and holidays. And I was looking at the challenges solutions and the append can't do more than 16 records for the source. I have a file that gives me the whole calendar year and tells me what are weekend and what are holiday. 

Example

Day in Hold     Report Date Time             Original Date

54                    2023-08-28                       2023-07-05
54                    2023-08-28                       2023-07-05
3                      2023-08-28                       2023-08-25
243                  2023-08-28                       2022-12-28
52                    2023-08-28                       2023-07-07
52                    2023-08-28                       2023-07-07

48                    2023-08-28                       2023-07-11
66                    2023-08-28                       2023-06-23
5                      2023-08-28                       2023-08-23
1                      2023-08-28                       2023-08-27