If you have a separate table of non-working days, with one row per day, you can achieve it with custom SQL which selects a count of non-working days between your start date and end date.
You may want to differentiate between weekend days and custom holidays in some way.
I'm thinking of a data-source with custom SQL something like this (the date calculation is probably not even correct SQL and likely to be SQL dialect dependent - but you get the gist):
SELECT id, start_date, end_date,
(DATEDIFF('day', p.start_date, p.end_date) - (SELECT count (*)
FROM non-work-days n
WHERE n.date BETWEEN p.start_date AND p.end_date)) AS number_of_working_days
FROM project p
There's also the pure-calculation option. Start with a basic DATEDIFF, subtract out weekends, adjust for the day-of-week of your start & end days, then subtract out holidays that fall between your start & end.
Gets a bit complicated, but doable. :) Here's a solution for everything but subtracting the holidays: http://www.tableausoftware.com/forum/using-datediff-only-weekdays
Richard and James,
Those are two great solutions!
Thank you :)
Is there a way to do holidays?
DATEDIFF("weekday",[Calculation1],[Calculation2])-2*(DATEPART('week', [Calculation2]) -DATEPART('week', [Calculation1]))
Thank you Joe - couldn't find it.
If you've got access to write some custom sql, this should do the trick:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = 'some date'
SET @EndDate = 'some date'
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
Thanks for sharing the SQL, but I am not sure that would be ideal, it looks similar to James' initial option that does not work in all cases.
Also this still does not address the issue of handling for holidays.
The solution that I use is an additional table with a list of valid working dates, similar to Richard's with a RAWSQL pass-through function, and this solves for both.
I tried to find the soultion to get a count of workdays based on the link attached but did not see it