22 May 2017
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*5Part 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 :)