Replicating Excel WORKDAY Function in Alteryx - feel free to use it if useful

[IPWE]

So I'm quite new in Alteryx and been moving reports from manual tools (access/Excel) to Alteryx. 

Just realized that there is no function in Alteryx to easily calculate x amount of working days from a starting date (e.g. 28th Oct 2016 + 2 days = 1st of October 2016) so decided to write my own one (unless there is one in that case I wasted an hour of my time for this).

Then I found out that I can’t do while loops (Like I said, new in Alteryx), so I managed to do one without a while loop, 

The basic of the formula is this:

Start Date + (Floor( ((Daysof Week+DaysToAdd)-1/5)*2) + DaysToAdd = end date

If the Start Date is Sat/Sun then start from previous Friday.

Feel free to use it if you guys think it is useful.

[dataMack]

The problem with 'workdays' is that they tend to have some specificty to your geographic region and even your specific company.  I'm in the US and work for a bank, so there are about a dozen weekdays throughout they year that shouldn't get counted as 'workdays' if I was building something like an analytics process to track projects.

I would consider inputting a calendar datasource (make your own in excel if you have to, then input it to Alteryx) that had full identification of each day as a workday or not and then you could blend that and use it for a more precise calculation.

[IPWE]

The problem is that I am trying to port alot of manual reporting to Alteryx. And some of them use Workday. I was trying to find the best way without the use of a lookup table outside the public holiday calculation. 

This solution works for my organization's needs and if anyone think it is useful then feel free to utilize it. I am not implying that this solution fits everyone's requirement so if you don't think it is useful then you simply don't need to use it. 

[IPWE]

attached the workflow also

[Paul_C]

This is a Macro that can be used like the WORKDAY formula from Excel.

[KeithH]

I noticed the above macro had some issues when calculating some end dates, not sure why. I build my own which seems to work correctly and adds some functionality. This replicates the Workday function in excel.

[pranit1997]

Hi

The macro isnt working for me.

I have a field named Entry Date - based on which I need to define the workdays.
I am very new to Alteryx, so if you could also specify the configurations needed for the macro.
Can any one of you help me out.

Thanks in advance.

@KeithH @KeithH @Paul_C 

[jyeh]

I am including yet another implementation.

I would suggest creating a batch macro called Institutional Holidays, with the following inputs:

1) An input tool with a list of date type considered holidays.

2) Macro Input; Date, # of business days to add positive or negative, and composite primary key (CPK)

Add the Institutional Holidays batch macro in your workflow indicating the inputs needed and join the output based on CPK to continue processing.

Hope this helps.

[Mond]

ty for sharing the macro! i don't know what does CPK mean?

if i don't want to specify a holiday, will the macro still work?

[Mond]

Hi keith - your macro is interesting, but i'm getting a date input error when i hook it up to my data:

Start Date	Days
12-10-2021	-1
12-16-2021	-1
10-04-2021	-1

i haven't hooked up anything to the holiday input and i have selected to exclude saturday and sunday.