Datetimediff using only weekdays/business days

[JessicaAddy]

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

[jdunkerley79]

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

[BCarley]

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.

Thanks!

Ben - BIPB

[RodL]

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.

[ssjl]

Is there any chance you could perhaps upload a 9.5 version?  Thanks so much!

-s

[jdunkerley79]

Not sure if this will work but try this

[ssjl]

That's got it! 

Thank you very much...

-s

[Mitchell_G]

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!

[jdunkerley79]

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

[RodL]

Attached is where I turned the workflow into a batch macro.

I think this will get you close.