Aging Calculation taking Holidays into consideration

Question

Hello - I have two columns of dates that I am trying to calculate aging while removing weekends and holidays.  I have a file showing all holidays for my company that I have added into the workflow as a text input.  I would like to do something similar to the Networkdays calculation in excel.  I know Alteryx does not do this same type of thing.  Is there a way I can calculate this in the attached file (Closed-Created)?

Aging - Minus weekends and holidays.xlsx

Holidays.xlsx

cgoodman · Answer

I am on my phone at the moment so I can’t help with screenshots, but based on the information provided I’ll try and do my best:

if I look at Networkdays in excel you have three parameters:

=NETWORKDAYS(start_date, end_date, [holidays])

The function uses the following arguments:

* Start_date (required argument) – Start_date can either be earlier than end_date, later than end_date, or same as end_date.
* End_date (required argument) – The end date.
* Holidays (optional argument) – This specifies the list of holidays that should be excluded from the work days calculation. We can enter it as a range of cells that contain the holiday dates (that is F2:F4) or as a list of serial numbers that represent the holiday dates.

To replicate this using Alteryx:

1) add a record ID to your data so you have something to groupby later

2) using the generate rows tool you can densify your date by creating a date record for every date between the [Created] and [Closed] date.

3) if you join your densified dataset (left input) against your list of holiday dates (right input) you can use the join as a filter. Dates that match will come out of the J output, but dates that don’t match will come out the L output (hence you’ve removed the holidays

4) for each date you can use a formula tool to get the day of the week. DateTimeFormat([DateField],"%u") will give you the day of the week where Monday = 1 and Sunday = 7.
5) Filter for day of the week being <6 ie a weekday

6) use a summarise tool to group by record ID and count of the dates to get the equivalent of Networkdays

AngelosPachis · Answer

Hi @ebowlan ,

In this post, I have created a workflow that replicates the Networkdays formula of Excel

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/how-to-convert-network-formula-and-MOD-formula-to-alteryx/m-p/717382#M168848

Maybe you can use that for your instance?

Thanks,

Angelos