Excel Function Workday and Networkdays in Alteryx

[Gr4c3Sult]

Hi Community!

I am automating a large repeatable report that is hundreds of thousands of rows.

I have searched and looked at several workflow solutions proposed for Workday and Networkdays.. but my date in Workday can be any working day and my start date in Networkdays can also be any day of the week.  I haven't seen any solutions that will accommodate my scenario..  If there is one out there that I have missed please share it with me 🙂  

Steps to bring out the AP Date is put the formula =WORKDAY(Vendor Paid Date,-2, Holidays)

Steps to bring out the “Days 2 Pay Put the formula =Networkdays(Accounting date, AP Date, Holiday Table)-1

Any help is greatly appreciated!!

Grace

[Gr4c3Sult]

@mst3k 

I need to solve for AP date by subtracting 2 work days and any holidays in excel it would be formula =WORKDAY(Payment Date,-2, Holidays)

Then solve for Days 2 Pay by subtracting the Accounting date from the working days AP Date less any holidays.. problem is sometimes the AP date will be less than the Accounting date so it will need to be a negative value resulted.

in Excel it would be formula =Networkdays(Accounting date, AP Date, Holiday Table)-1

The overall goal is to make sure we are paying our vendors within our contracted terms.  We pay them once we are paid plus business days PWP 10 PWP 5 etc. and holiday's don't count.  Acct Date is when we are paid, Payment date is when the funds settle in there account.  AP date is the day the payment was initiated.

Appreciate your help!! I was really excited about your first workflow, I thought it was going to work.. But as you noted the rows when the AP date is before the Accting Date result in null.  I wonder if we can solve for that... 

Thanks!

Grace 

[Gr4c3Sult]

@oly ,

Hi Thank you for the suggested solution.  My data can have well over 100K rows of start and end dates. generating rows for all the dates in-between would be a monster, my server admin would kill me if I published it.  Also there are a number of nuances that this workflow doesn't address.  

Thanks again!!

Grace

[Gr4c3Sult]

@mst3k 

I just realized a solution.  I'll throw a formula tool on and If Null then "Paid before Paid" YAY!!

Would you be able to solve for the AP Date?

THANKS!!!! 

Grace

[mst3k · Accepted solution]

here, i packaged it into a macro. so you can pick which date fields you want to compare, and reuse it as many times as you need to calculate between other dates

the holiday connector is optional. if you use it, you'll have to pick the field map on the macro to select the field name to map into it. if you don't use the H input, you don't have to map anything in that dropdown in the macro even though it appears red. this should run fine as is - don't be surprised in the event something shows a ! error before you run it, i just threw this together and sometimes macros think funny when they haven't been run yet.

[mst3k]

oh and definitely sample it and test a few out in excel just to make sure it's getting the right answers... i did that myself but you should probably check too

[Gr4c3Sult]

@mst3k 

Thank you for all your help!!  I am wondering if this Networkdays solution can be modified to  solve =WORKDAY(Payment Date,-2, Holidays)?

What you did in the Networking day workflow is beyond my skill set.  I have so much to learn!!

Thanks again for your time 🙂

Grace