Finding Work Days and Omitting Holidays

If you ever work with Technology on Projects, you often wonder how many days you have for a certain project. How would you go about finding the actual work days minus the weekends? What about omitting Holidays?

In Excel you would use the Network Days formula. In Alteryx you would do something a little different.


I have a list of dates in my workflow with an Text Input Tool.


I will add the DateTime Tool to this Text Input Tool. I will select the MM/dd/yyyy format.


I add a Formula Tool after the DateTime Tool with the below formulas;


WeekDayName

DateTimeFormat([DateTime_Out],'%A')


WeekDay

IF [WeekDayName] IN ('Sunday','Saturday') THEN 0 ELSE 1 ENDIF


This will add a 1 to any days that are weekdays. *Ensure your WeekDay formula is formatted as an Integer.



I also add all of the Holiday dates for the year so I can filter these out for a project. So I add another Text Input Tool to the workflow with the list of Holidays for the year.


I attach another DateTime Tool to this Text Input Tool with the same configuration as above.

I then add a Join Tool and join them on the DateTime_Out field.

If I click on the Join Output Anchor (J), the results will return any dates that are Holidays only.



I will add a Summary Tool to the Left Output Anchor (L) of the Join Tool. By using the Left Output Anchor, I will omit any Holidays that were found. I will Sum the WeekDay column with this tool to get the number of working days for the time period I entered.



21 views0 comments