Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

We’re aware of an intermittent issue with our My Alteryx login and are actively working to have the issue corrected. If you run into an error when logging in, please try clearing all cookies or accessing the community on a different browser. Thank you for your patience!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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

IPWE
5 - Atom

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.

12 REPLIES 12
dataMack
12 - Quasar

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
5 - Atom

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
5 - Atom

attached the workflow also

Paul_C
5 - Atom

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

KeithH
6 - Meteoroid

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
7 - Meteor

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
7 - Meteor

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
6 - Meteoroid

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
6 - Meteoroid

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.

Labels