Is there a way to calculate the number of weekdays/business days between two dates? I have spent some time searching the internet and the Alteryx Community, but cannot find any solutions to this.
Thanks!
Jessica
I am not sure if this is the best way to do it but is a way I have tried in the past and think works. Basically a port of the C# code on StackOverflow to an Alteryx formula.
Assuming the two dates are StartDate and EndDate.
First I needed to get the weekday as an integer with 0 for Sunday and 6 for Saturday (just to reproduce .Net behaviour). A simple enough case statement after formatting the Start and End Date:
Switch(DateTimeFormat([StartDate],"%a"), 0, "Sun",0, "Mon",1, "Tue",2, "Wed",3, "Thu",4, "Fri",5, "Sat",6)
Having created a StartWeekday and EndWeekday the business day function is:
IIF([StartDate]>[EndDate], Null(), 1 + ((DateTimeDiff([EndDate],[StartDate],"days")*5 - ([StartWeekday]-[EndWeekday])*2) / 7) + IIF([EndWeekday]==6,-1,0) + IIF([StartWeekday]==0,-1,0))
Attached as a workflow.
Update: There was a typo in the initial version, have corrected it and updated the yxmd file.
Hope it helps
James
Hello,
I've uploaded a macro to the gallery here for you to use if you'd like. I've used a similar flow to James but I forgot about Alteryx's DateTimeExport feature, instead I used the fact that 1900-01-01 was a Monday and used the mod of the difference in days to find what day I'm starting on!
I think from then on we did things very similar but for completeness, I found the whole number of weeks by rounding down the number of days between start and end divided by 7 (using the floor() function). From there I found the number of "extra days" i.e. the days that do not fall into a complete week.
Using the zero indexed start day (Mon:0, Tues:1 ...) you are able to see how many non-weekdays in involved, also taking into acount the starting day. If StartDay + Extra Days is less than 4, you have only weekdays, if equal to 5, you must have 1 non weekday, if greater than 5, you only have weekdays.
The macro could have been reduced into one formula but I've expanded it out for clarity.
Ben - BIPB
Another approach, but I think worth showing here, points out the ability in Alteryx to format a date/time into just about any configuration you can think of using the format specifiers.
In the attached app, I use that functionality to format a date as "Monday", "Tuesday", etc. Then all you need is a simple filter tool to take out Saturday and Sunday.
I have also added in the functionality in the app to designate a file for holidays if you want to include that.
I like this approach, it's very simple. I'm new to Alteryx, any adivce on how to use this method to calculate the working days between two dates for each record?
I have ~10,000 records. There are two date columns, for simplicity, starting date and ending date. I want to leverage your method to show me the working days date difference for each record.
Thanks for the help!
You just need a formula tool set up with the formula above.
Are you just wanting a weekday count or do you need holidays as well?
If you can post some sample data will put together an example for you
Hi jdunkerley79,
Would you please explain this part:
1 + ((DateTimeDiff([EndDate],[StartDate],"days")*5 - ([StartWeekday]-[EndWeekday])*2) / 7) + IIF([EndWeekday]==6,-1,0) + IIF([StartWeekday]==0,-1,0)
Regards,
MJ
I've been using jdunkerley79's solution and its working great until I ran into negative numbers. It just pulls Null if negative and I need the actual result (the negative number). Does anyone know a fix for this?
