How to calculate NETWORKDAYS in Tableau

Question

In my last post, Convert Original Date and Time to Valid Work Date and Time , I posted a how to change the date and time of a date-time field to a valid working date and time in Tableau. This was helpful for data that lands before and after work hours. Unfortunately, at that point if you used DATEDIFF() function in Tableau, the formula would still count the weekend days between the start date and end date.

So, doing some reseach I found this post by Ian Baldwin from 2017 How to Calculate Working Days between dates in Tableau. I tried it but it only worked for the first 8 days. After 8 days I would get a Float value instead of an Integer value (2.48005 and so on). Then, I went to Excel and started to run some numbers and analyzed how NETWORKDAYS() in Excel actually works. So what to do next?

Answer 1

This is what I came up with:

//Calculate the difference between start date and the end date and add 1 day.
//for example from 2/26/2021 to 3/15/2021 = 17 + 1= 18 days
((DATEDIFF('day',[Date X],TODAY()) + 1)

//substract the total amount of days for each weekend between start date and end date.
//for example: (Round(18/7,0)*2 = 6
- (ROUND((DATEDIFF('day',[Date X],TODAY())+1) / 7,0) * 2)

//whatever result comes out of the above, we will substract and additional 1 or 2 days depending
//if the start date falls on a saturday or a sunday.
- (CASE DATENAME('weekday',[Date X])
WHEN '7' THEN 2
WHEN '1' THEN 1
ELSE 0 END)) - 1

The solution above is consistent, accurate and reliable (well, still testing it). I added the '-1' to take off the extra day that Networkdays counts (just like it's twin sister in Excel). If you have a better solution, please let me know.

Answer 2

If you are looking to calculate the number of hours between a start date and time against an end date and time, here is what I came up with:

(DATEDIFF('hour',[yourStartDate],[yourEndDate]) + (24))
//example: DATEDIFF('hour', startdate, enddate)= 
//(17 days * 24 hrs/day) + 24 hrs = (18 days * 24 hrs/day)

//Then,substract the total amount of days for each weekend
// between start and end date (in hours)

-(ROUND((DATEDIFF('day',[yourStartDate],[yourEndDate]) + 1) / 7, 0) * (48))
//ex: datediff('day',startdate, enddate)=17 + 1 = 18 days. 
//Next, 18 days / 7 days = ROUND(2.57, 0) = 2. Finally, 2 days * 48 hrs/days

//find which weekday the start date represents
-(CASE DATENAME('weekday',[yourStartDate])
WHEN '7' THEN (2*24)//falls on Saturday, then substract (2 days * 24 hrs/day)
WHEN '1' THEN (24)//falls on Sunday, then substract 24 hrs
ELSE 0 END)//any other day, substract 0 hrs

This will calculate the number of hours for only working days. For example, if start date and time is 3/19/2021 7:00:00 and end date and time is 3/22/2021 7:00:00, the result should be 24 hours. Again, if you find a better solution for Tableau Desktop. Please, let me know.