Work backwards from a given date to return two workdays prior

[taschmies]

I have a list of ReviewDates and holidays/non-working days for 2025/2026. I need to return two different dates, which should be calculated as follows:

PrepDate1 = ReviewDate - 2 working days

PrepDate2 = PrepDate1 + 1 working day

The PrepDate1 needs to be exactly two workdays before the review date, so if the ReviewDate is 5/27/2025, then PrepDate1 would be 5/22/2025, since it would not count Memorial Day, Saturday, and Sunday as workdays. PrepDate2 would then be 5/23/2025. If there was a holiday or weekend between PrepDate1 and PrepDate2, then it would skip over those dates and land on the next working day for PrepDay2.

I saw some similar community solutions when searching, but I had difficulty modifying them to fit my requirements exactly. I also saw that apparently there is some Alteryx function called Networkdays which allows for a holiday input, but I couldn't figure out how to add this function to my version of Alteryx.

See attached for the excel docs of ReviewDates and holidays. Thank you in advance for any guidance you can provide!

[binuacs]

@taschmies one way of doing this

[Qiu · Accepted solution]

@taschmies 
I will go with a bit brutal force approach.

First, I expand the review date to 7 days ago with Generate Rows tool, then filter out the Weekends.
Join with the NoWorking Day data, and the dates coming out from L anchor are the "Candidates date" for PrepDate.

I noticed that there are some duplicated dates in your Review Dates, so I join back to it at the last.

I found it quite interesting, can I submit this as a weekly challenge idea?

[taschmies]

Thank you for your help! Yes, feel free to use this idea as a weekly challenge.