Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Regularly on the community, there are questions about how many working days there are between two dates. While Alteryx now offers a DatetimeWorkdays function in the formula tool (for 2025.1), it doesn’t take into consideration bank/public holidays.
I wanted to write a blog about how you could generate a list of working days in the calendar year.
Step 1: Create a list of all days in the calendar year (Only Monday -Friday)
Note: if you have slightly different working day schedules, the formulas might need to be updated.
There are 3 main ways to create this:
a) Generate rows for all days of the year, and then only keep Monday-Friday
In the Generate Rows tool, have a starting (initialization) expression for the first January of the current year. This can be done with the following expression:
todate(tostring(datetimeyear(datetimestart()))+"-01-01")
Be sure to create a new field and data type as Date.
Each row we would like to be the following day, this is done with the following loop expression:
datetimeadd([date],1,"day")
We want the days to be for this year; hence, we want to stop on December 31st of the current year. This is modeled in the condition Expression like so:
[date] <= todate(tostring(datetimeyear(datetimestart()))+"-12-31")
Subsequently, we need to filter out all days that are either Saturday or Sunday (i.e. keeping only Monday – Friday). This is done with the filter tool and using the following custom expression:
!StartsWith(DateTimeFormat([date],"%a"), "S")
As only Saturday and Sunday start with S.
b) Generate dates using a TS Filler tool and then then only keep Monday- Friday
Please note that the TS Filler tool is not a default tool; it needs to be installed as part of the R predictive tools.
In the text input, start with the following information:
This will be used in creating our date range for the list we would like to create.
Use the formula tool to create the start and end of the year in date format, using the following expression:
ToDate(tostring(datetimeyear(datetimestart()))+[Date])
Ensure you are creating a new field and have set the new field data type to date. This will create a new field with the start and the end of the current year in date format.
In the TS Filler tool, select your newly generated date field and set the interval to day and 1 increment; this will give a list of all days in the year.
The list of days is in the newly created date field as calculated by the formula tool earlier.
Then, similar to the first method, filter out the days that are on the weekend.
c) Generate rows of only weekdays
Here we only create a row if the day is between Monday – Friday.
This method is the most complex out of the three, and starts by looking at January 1st for the year and determining if it is a Weekend. If so, it uses a different day, and this is captured in the starting (Initalization) expression like so:
If Datetimeformat( todate(tostring(datetimeyear(datetimestart()))+"-01-01"),"%a")="Sat" then todate(tostring(datetimeyear(datetimestart()))+"-01-03")
// If 1 Jan is a Saturday then start on the 3 Jan as that will be the first non weekend day of the month
elseif Datetimeformat( todate(tostring(datetimeyear(datetimestart()))+"-01-01"),"%a")="Sun" then todate(tostring(datetimeyear(datetimestart()))+"-01-02")
// If 1 Jan is a Sunday, then start on the 2 Jan as that will be the first non weekend day of the month
else todate(tostring(datetimeyear(datetimestart()))+"-01-01")
// 1 Jan is not a weekend day and keep it as 1 Jan
Endif
The loop expression is similar to method A, except that if the day is a Friday, then add 3 days (to skip the weekends and move to Monday).
This is done using the following expression:
datetimeadd([date], If StartsWith(DateTimeFormat([date],"%a"), "F") then 3 else 1 endif ,"day")
Please note we do not have to consider Saturday or Sunday, since with the starting expression and loop it will never generate a row on those days.
The Condition expression is the same as method A:
[date] <= todate(tostring(datetimeyear(datetimestart()))+"-12-31")
Quick summary for step 1
You have seen listed above 3 different ways to generate all weekdays within the calendar year. Method C (Generate rows of only weekdays) is the most efficient in terms of only using one tool. However, my personal preference is method A (Generate rows for all days of the year and then only keep Monday- Friday), due to its simplicity compared to method C. All three methods are in the workflow attached; however, only method A is enabled.
Step 2: Getting a list of all Bank/Public holidays
This will be done via an API, and will be using this one as an example for the UK.
Input the endpoint URL into a Text Input and then a Download tool. The Download tool might need different calibration based on the API you are using.
Then, parse the data and keep only the data you need (the filtering might not be needed based on the API and time region you are interested in). In this example, we are looking at bank/public holidays in England and Wales that are in the current year, and want to only keep the fields that contain the date information.
REGEX_Match([JSON_Name], "england-and-wales.events.\d+.date")
and StartsWith([JSON_ValueString], tostring( datetimeyear(DateTimeStart())) )
Then we need to transform the date data from string to date format using the formula tool and the following expression:
ToDate([JSON_ValueString])
Step 3: Joining the list of weekdays for the year and the list of bank/public holidays
Use a join tool to join the data flows from step 1 and step 2, joining on the date field.
For reference, the list of all weekdays is the left input, and the list of all bank/public holidays is the right input to the join. Going forward in the workflow, we will only be looking at the left output from the Join tool, as these will be all weekdays that aren’t bank/public holidays (if the inputs were the other way round i.e. bank/public holidays as the left inputs and the generated days at the right input, then we would be looking at the right outputs).
Step 4: Assigning the working day to the date
Subsequent to the Join (left output), use a Sort tool to organize the date in descending order (having January 1st as close to the first record). To assign a working day for the month, you want to start at 1 and increase by 1 until the month changes—this is captured using the Multi row tool with the following expression.
IIF(DateTimeMonth([Row-1:date]) != datetimemonth([date]), 1, [Row-1:WKD for month]+1)
Then, to determine the working days for the year, use a record ID tool.
Conclusion
These steps will provide you with a list of all working days in the UK for the current year. You might need to do some adaptation if the region you’re interested in doesn’t follow a Monday-Friday work schedule, as well as the API to get the relevant bank/public holiday information.
After running this workflow, you can save the output to an external source and call upon it as needed, or run it for each workflow (though this would take up processing power).
To calculate the working days between two dates, filter for the two days and then subtract the working days for the year (sneaky step 5).
