Calculate Working Days Between Two Dates in Spotfire and Alteryx

Facebook Tweet LinkedIn

Regular readers know I am doing a lot of work in Alteryx these days. Actually, our entire analytics team is. We collaborate a lot on Microsoft Teams. Today, one of my coworkers hit up the Teams channel and asked if anyone knew how to calculate working days between two dates in Alteryx. While I hadn’t done this in Alteryx, I did have a Spotfire support article on how to solve this problem using a single expression. Ultimately, we used the Spotfire support article to back into Alteryx calculations to make this work. This post will explain how to solve this problem in both Spotfire and Alteryx.

Spotfire Solution

You can find the Spotfire support article at this link. The link includes a downloadable DXP to use as a guide. The expression that solves the problem is shown below.

if(dayofyear([START DATE])=dayofyear([END DATE]),0,(Integer(DateDiff("day",[START DATE],[END DATE]) / 7) * 5) + 
DayOfWeek([END DATE]) - DayOfWeek([START DATE]) + (if((DayOfWeek([END DATE]) - DayOfWeek([START DATE]))<0,5,0)))

To explain it, I’ll break it down into four parts.

Part 1

The first part of the expression is addressing the condition where the start and end date are the same and the result should be zero. Super easy so far.

if(dayofyear([START DATE])=dayofyear([END DATE]),0

Part 2

The second part is the date diff between the start and end dates and some basic math to help account when the dates are more than a week apart. Assuming the start date is before the end date, place the start date first in the expression. The result will be positive.

(Integer(DateDiff("day",[START DATE],[END DATE]) / 7) * 5)

Here’s a simple example.

Note, the date diff result is a round number, but the data type is real. That is because the date diff is wrapped in the Integer function. Thus, an integer is being multiplied by 5, resulting in a round number. This ensures the calculation rounds correctly. (We had to make adjustments for this in Alteryx.)

(Integer(DateDiff(“day”,[START DATE],[END DATE]) / 7) * 5)

Part 3

Next, subtract the start date day of the week from the end date day of the week.

 DayOfWeek([END DATE]) - DayOfWeek([START DATE])

The DayOfWeek function will return the day (i.e. Mon, Tue, Wed) in the cell, but note the data type is an integer. The integer will be a value between 0 and 6. Sunday = 0. You can find that in TIBCO documentation here.

Part 4

Again, we subtract the start date from the end date, but an if statement is added that helps account for when the dates span more than one week.

(if((DayOfWeek([END DATE]) - DayOfWeek([START DATE]))<0,5,0))

Here is how the math breaks down for the entire expression.

Before wrapping up Spotfire and moving on to Alteryx, here is the same set of expressions with a few more examples. I am including this to show you that when subtracting the start day of week from the end day of week, it’s possible to get an empty result. This is because the result is negative and there is no “negative day” to show, so the result is empty. However, the expression still works.

Now that I’ve explained the Spotfire solution, let me show it in Alteryx.

Alteryx Solution

The Alteryx solution to calculate working days looks very similar with a few modifications to account for how Alteryx expressions work. They aren’t exactly the same as Spotfire.

First, we entered in the date diff. You’ll notice the end date comes first rather than the start date in the Alteryx expression. Then, we used the Floor function to get the rounding right. Without the Floor function to round down, the value was off.

Next, we have to translate “Sun”, “Mon”, etc into an integer for both the start and end date.

Finally, we added in the IF statement.

And here you can see we have matching outputs.

Wrap Up

Now, you know how to calculate working days between two dates in either Alteryx or Spotfire.

Spotfire & Alteryx Versions

Content created with Spotfire 10.2 and Alteryx…..

If You Enjoyed This…

Check out my other posts on Alteryx and Spotfire expressions….

Post Views: 5,255