True days in past

Question

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

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

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

Madzy · Answer

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

JosephSerpis · Answer

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

Prometheus · Answer

@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/484513