Tableau has native function to calculate difference between two dates and also to add days (week, months, etc.) to a date. However, by using this function, we calculate all the days including weekends. In the real world, we know that business days are normally calculated from Monday to Friday, without Saturday and Sunday.
1. Calculate business days between two dates
The formula to calculate this case is
DATEDIFF('day', [startdate], [enddate]) + 1 - (2 * DATEDIFF('week', [startdate], [enddate]))
The first part calculate all the date difference -> DATEDIFF('day', [startdate], [enddate]) + 1
While, the second part takes care of the weekends that should be excluded. Put it into separate calculated fields in case you want to see what those formulas do.
2. Adding business days to a date
The second case here aims to calculate scheduled completion date. It requires one more step than the first case, namely, when the added date falls in the weekend. In this example, we add five days to a startdate.
DATEADD('day', 5, [startdate]) – 1 + DATEDIFF('week', [startdate], (DATEADD('day', 6, [startdate]))) * 2
The principle is the same as the first case. And then, we have to make sure that after being added by five days, it doesn’t fall into weekends. For that, we can write simple IF-statement.
IF DATEPART('weekday', [scheduled_completion_date]) = 1 OR //Sunday
DATEPART('weekday', [scheduled_completion_date]) = 7 // Saturday
THEN DATEADD('day', 2, scheduled_completion_date])
ELSE [scheduled_completion_date]
I hope, it helps!
Cheers,
Nuki