22 May, 2017| Ian Baldwin
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.
) / 7*5
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:
Then we trim off days in the first partial week of the time frame. Subtracting 7 minus the weekday number takes us upto the end of the first Sunday.
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.
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.
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.
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.
Thanks for reading 🙂
21 thoughts on “How to Calculate Working Days Between Dates in Tableau”
Ian, see if you can figure out who to exclude bank holidays from the working days!
Thank you very much !!!
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.
Excelent, I would also love to see a holiday calendar included as an option
Thank you Ian!
Great explanation of the great solution.
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
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.
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?
I tried the above and it seemed to be off by a day. Here is what I use:
//Return zero based number of working days from start date until today(). Analogous to Excel formula Networkdays(). Note:Online solutions break for small duration which spawned the creation of this function.
//Assume whole 5 work day weeks, then subtract extra.
MAX((DATEDIFF(‘week’,[Start Date], TODAY()) + 1) * 5 //Assume at least 1 week
//Remove extra from start
– MAX(DATEPART(‘weekday’,[Start Date]) – 2 ,0)
//Remove extra from end
– MAX(5 – (DATEPART(‘weekday’, TODAY() – 1)),0),0)
Hy Ian, thanks for the formula it was very helpful , i just have one Question, would it affect the formula if my server ( data source) is configured so that the first day of the week is monday …
the formula gives me bad values for sundays only, and changing the date settings on the data source or the regional settings on my PC didnt fix the problem, i also tried putting sunday or monday in the DATEPART(‘weekday’,[date 1],’sunday/monday))
but it also didnt seem to do the trick…?
Thanks for the solution but if you can take time for my small problem.
My problem is that I need the count of days from two different dates let it be [D1] and [D2] excluding the Saturdays and Sundays which are coming in the date range of the date difference.
For eg – If [D1] is on Saturday and [D2] is on Friday so the count of Sunday should be excluded from the result.
Any help would be appreciated.
Thanks in advance.
Great work – thank you for your help!
Hi Ian – This formula works good with calculating working days between dates in Tableau ,can you help with excluding Holidays as well to calculate just the working days.
How to compare Current Year measures to previous year same weekday value on daily level in table report format.
For example :
1st Jan 2018 Monday vs 2nd Jan 2017 Monday (Previous Year)
2nd Jan 2018 Tuesday vs 3rd Jan 2017 Tuesday (Previous Year)
And so on……
Great work. I have a question additional to this step. How do you add a logic which removes US holidays as well. The dates are mentioned below.
Can you help me get it reversed? This will be very kind of you. I need to find a target date which excludes Weekends and holidays. I have review date and different SLAs as per business unit.
Holiday list –
1. if Review date is 1/1/19 and SLA is 5 business days, then I should get target date as 1/8/2019 (excluding 1/1/19 holiday and weekends).
2. if Review date is 1/14/19 and SLA is 5 business days, then I should get target date as 1/21/2019.
You are a wiz! Just tested it on different timeframes and it worked like a charm
This is great and exactly what I needed. For everyone asking I was able to add holiday subtractions by adding this code to the end of the example given.
DATE(’01/01/2019′)>=[Start Date] and DATE(’01/01/2019′)=[Start Date] and DATE(’05/27/2019′)<=[End Date]
just keep repeating for each holiday date. There may be a better way to do this but for now its working for me.
I’m confused to where 5/27/2019 came from on your formula above?!?! Can you expand in depth if you are still following? Or maybe some else can? Really don’t mind making an if then statement for each of the holidays since my range will only be six months or so.
I am getting -1 as number of business days, when my date starts and ends on the same date. Can you please tell me why that must be happening
Thanks Ian for the post. I had an error whenever the second date was a saturday or sunday. I tweaked the formula by not deleting the 1 at the end only if it a week day. Here is the formula :
(DATEDIFF(‘day’,[First day in the month],[First day in the following month]) –
(7-DATEPART(‘weekday’,[First day in the month])) –
DATEPART(‘weekday’,[First day in the following month])
) / 7*5
+ MIN(5,(7-(DATEPART(‘weekday’,[First day in the month]))))
+ MIN(5,(DATEPART(‘weekday’,[First day in the following month])-1))-
iif(DATEPART(‘weekday’,[First day in the following month])=6 OR DATEPART(‘weekday’,[First day in the following month])=1,0,1)
Leave a Reply
- DATEDIFF( "week", [start], [stop] ) * 5.
- - MAX( DATEPART( "weekday", [start] ) - 2, 0 )
- + MIN( DATEPART( "weekday", [stop] ) - 1, 5 )
- Count the number of days between the two dates.
- Subtract the number of weekend days between the two dates.
To give you a clear idea, Count of business days = Days in the calendar - weekends - Holidays. Hope someone helps me out with this issue. You may choose datetruncs other than months (quarters, weeks, etc.) It only works if there are at least 2 holidays between Start & End dates.How DAX calculate number of working days between two dates? ›
DAX functions like NETWORKDAYS and WORKDAY can be used to calculate the number of working days between two dates in Power BI. These functions take into account weekends and holidays when making calculations, ensuring that working day data is accurate and reliable.How do you calculate Networkdays in tableau? ›
- DATEDIFF('day', [Order Date], [Ship Date]) + 1.
- - 2 * DATEDIFF('week', [Order Date], [Ship Date])
The NETWORKDAYS function in Excel returns the number of workdays between two dates, excluding weekends and, optionally, the holidays you specify. The first two arguments are obligatory and the third one is optional: Start_date - initial date from which to start counting working days.