How to Calculate Working Days Between Dates in Tableau

I first wrote this blog post here: https://til.bi/2ogP9UA

This is a question that comes up quite regularly when working with dates in business analytics. How can I calculate date differences in working days? Here’s the code for the Tableau calculation in full and I break it down beneath.

(DATEDIFF('day',[Day1],[Day2]) -
(7-DATEPART('weekday',[Day1])) -
DATEPART('weekday',[Day2])
) / 7*5
+ MIN(5,(7-(DATEPART('weekday',[Day1]))))
+ MIN(5,(DATEPART('weekday',[Day2])-1))
-1

Part 1: Working Days in Full Weeks Spanned

This gives us the number of working days in full weeks spanned. First of all it calculates the absolute date difference between the start and end date here:

(DATEDIFF('day',[Day1],[Day2])

Then we trim off days in the first partial week of the time frame. Subtracting 7 minus the weekday number takes us up to the end of the first Sunday.

- (7-DATEPART('weekday',[Day1]))

Then we trim off the days in the partial week at the end of the time frame to take us back to end of the last full week.

- DATEPART('weekday',[Day2])

Now we have a number that is a multiple of 7 and will reflect the number of days in the full weeks between the start and end date. We divide this by 7 and multiply it by 5 to get the number of working days in these weeks.

) / 7*5

Part 2: Working Days in the First Partial Week

By default Tableau counts Saturday as the 7th day of the week and we want to count backwards how many working days have led up to it from our start date [day1] in our first partial week. This means if we take away the weekday number from 7 we will get the number of days up to Saturday. to stop us getting a count of 6 working days when looking at Sunday through to Saturday, the datepart snippet is wrapped in a ‘min’ calculation with the number 5 so we get no more than 5 working days in a week.

+ MIN(5,(7-(DATEPART('weekday',[Day1]))))

Part 3: Working Days in the Final Week

As Tableau counts Sunday as day 1 in its weekday calculations we need to subtract 1 from our weekday calculation on our final date [day2] for the final week working days. This makes the Sunday through to Saturday counting go from 0 to 6. Like before in the first partial week, we need to then wrap the weekday calculation in a ‘min’ formula with the number 5 so we get no more than 5 working days in a week.

+ MIN(5,(DATEPART('weekday',[Day2])-1))

Part 4: Making the Calculation Exclusive of the Start Date

At the moment the calculation is inclusive of both the start and end dates. This means a project starting and ending on the same day has a working days count of 1 day. If we want to look at full working days so that something starting on Wednesday and finishing on Thursday counts as 1, and a same day project counts as 0 days then we need to subtract 1 from our calculation.

-1

Thanks for reading 🙂

8 thoughts on “How to Calculate Working Days Between Dates in Tableau

  1. Very informative, though I would expect nothing less of a fellow Ian. I use something very similar to this but I think your version is a bit cleaner. I would also love to see a holiday calendar included as an option, this is one of the worst things we have to accommodate for.

  2. Great job Ian! This is very helpful! Is there a way to also incorporate hourly differences? For example if my [Day 1] = 2/9/18 9:52 AM and [Day 2] = 2/14/18 11:21 AM

  3. I dont get this. With your formula for the working days within the full weeks you are getting first the difference between two dates: Lets use an example between Jan 1rd, 2017 and Jan 31th, 2017. You get “30” days. Then you get the number of days until next Sunday after the first date: “6” and then, minus the day of the second date, which is Wednesday, so therefore is “4”. So your formula is doing:
    30-6-4 = 20, which is wrong! In January 2017 there are 3 full weeks, so the formula should return 21, then divided by 7 and multiplied by 5 would give 15, which is the right answer.

    1. Hi Mora, when I try this for January I get 30-5-4=21 for the first section. I can’t replicate what you’re getting. Maybe we have different default DateTime settings in Tableau?

Leave a Reply

Your email address will not be published. Required fields are marked *