4 Replies Latest reply on Sep 11, 2018 7:53 AM by Nisha Gala

I am having issue with business Dates calcuation on Tableau.Total number of iteration days. The below caluating is including weekends. How do i remove "Saturday" and "Sunday".

DATEDIFF('day',[Iteration Startdate], [Iteration Enddate])

• ###### 1. Re: Business Dates calcuation

Here's the solution I use:

```DATEDIFF('day',[StartDate], [Date])
-
(DATEDIFF('week',DATETRUNC('week',[StartDate]),DATETRUNC('week',[Date]))) * 2
-
IIF(DATEPART('weekday', [Date]) = 1, 2, 0)
-
IIF(DATEPART('weekday', [Date]) = 7, 1, 0)
+
IIF(DATEPART('weekday', [StartDate]) = 1, 2, 0)
+
IIF(DATEPART('weekday', [StartDate]) = 7, 1, 0)

```

Brought to you by James Baker.

--Shawn

• ###### 3. Re: Business Dates calcuation

Yep, that Mr. Baker is a clever guy.

--Shawn

• ###### 4. Re: Business Dates calcuation

Hi All,

I have similar need but in this case I am trying to calculate business days that has passed within the month. How do I do so?

I need them because I need to calculate the daily run rate of order and here are the steps/formula I have used which aren't really working well. In addition, is there a way to include holidays that falls into business day within the calculation as well. For example Sept 3 was a holiday in the USA and hence is not a valid business day.

1. Days Current Month = (DATEPART('day', TODAY() ))

2. avg days curr month= AVG([Days Current Month])

3. Weekend Days = If [avg days curr month] >= 28  Then 8

ELSEIF [avg days curr month] >=21 Then 6

ELSEIF [avg days curr month] >=14 Then 4

ELSEIF [avg days curr month] >=7 Then 2

ELSEIF [avg days curr month] = 6

AND ([Day of Week] = "Friday" OR [Day of Week] = "Saturday")

Then 1

ELSEIF [avg days curr month] = 6

AND ([Day of Week] <> "Friday" AND [Day of Week] <> "Saturday")

Then 2

ELSEIF [avg days curr month] = 5

AND ([Day of Week] = "Thursday" OR [Day of Week] = "Saturday")

Then 1

ELSEIF [avg days curr month] = 5

AND ([Day of Week] = "Sunday" OR [Day of Week] = "Monday"

OR [Day of Week] = "Tuesday" OR [Day of Week] = "Wednesday")

Then 2

ELSEIF [avg days curr month] = 5

AND ([Day of Week] = "Friday" )

Then 0

ELSEIF [avg days curr month] = 4

AND ([Day of Week] = "Wednesday" OR [Day of Week] = "Saturday")

Then 1

ELSEIF [avg days curr month] = 4

AND ([Day of Week] = "Sunday" OR [Day of Week] = "Monday"

OR [Day of Week] = "Tuesday")

Then 2

ELSEIF [avg days curr month] = 4

AND ([Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

Then 0

ELSEIF [avg days curr month] = 3

AND ([Day of Week] = "Tuesday" OR [Day of Week] = "Saturday")

Then 1

ELSEIF [avg days curr month] = 3

AND ([Day of Week] = "Sunday" OR [Day of Week] = "Monday"

)

Then 2

ELSEIF [avg days curr month] = 3

AND ([Day of Week] = "Wednesday" OR [Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

Then 0

ELSEIF [avg days curr month] = 2

AND ([Day of Week] = "Monday" OR [Day of Week] = "Saturday")

Then 1

ELSEIF [avg days curr month] = 2

AND ([Day of Week] = "Sunday")

Then 2

ELSEIF [avg days curr month] = 2

AND ([Day of Week] = "Tuesday" OR [Day of Week] = "Wednesday" OR [Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

Then 0

ELSEIF [avg days curr month] = 1

AND ([Day of Week] = "Sunday" OR [Day of Week] = "Saturday")

Then 1

ELSEIF [avg days curr month] = 1

AND ([Day of Week] = "Monday" OR [Day of Week] = "Tuesday" OR [Day of Week] = "Wednesday" OR [Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

Then 0

ELSE 0 END

4. Business Days = [Days Current Month] - [Weekend Days]