Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Datetimediff using only weekdays/business days

JessicaAddy
5 - Atom

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

21 REPLIES 21
jdunkerley79
ACE Emeritus
ACE Emeritus

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

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
Alteryx Alumni (Retired)

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
8 - Asteroid

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

-s

jdunkerley79
ACE Emeritus
ACE Emeritus

Not sure if this will work but try this

ssjl
8 - Asteroid

That's got it! 

Thank you very much...

-s

Mitchell_G
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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
Alteryx Alumni (Retired)

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

I think this will get you close.

Labels