How to Compute Number of Weekdays and Weeks in Tableau?
Blog | January 16, 2023 | By Karishma Vadher
There is no straightforward method in tableau to calculate the number of weekdays. However, this is an important information we need, to implement in many of our dashboards and reports.
I would like to share a simple yet efficient method to get this information in a correct way.
Firstly, to get weekdays between two dates, you need to have start and end day of the given period. Along with this, it is essential to have a tableau date function knowledge so, you can modify the logic according to the project or report requirements.
Following steps would help to formulate the weekdays and number of weeks in the Tableau where, “datepart” has been used (a discrete date function) and, a condition applied on 1st day i.e., Sunday and 7th day i.e., Saturday of the week. So, if the start or end date is the weekend, we are moving it ahead to Monday.
Step 1 – Start Date Formula:
IF DATEPART ('weekday', [Start Date]) = 1 THEN DATEADD ('day', 1, [Start Date]) ELSEIF DATEPART ('weekday', [Start Date]) = 7 THEN DATEADD ('day', 2, [Start Date]) ELSE [Start Date] END
Step 2 – End Date Formula:
IF DATEPART ('weekday', [End Date]) = 1 THEN DATEADD ('day', 1, [End Date]) ELSEIF DATEPART ('weekday', [End Date]) = 7 THEN DATEADD ('day', 2, [End Date]) ELSE [End Date] END
Step 3 – Number of days
Now, the 3rd step is to get number of weekdays. Here “MIN” and “DATEDIFF” two different Tableau functions. “MIN” is the string and “DATEFIFF” is the date function
MIN( DATEDIFF('day', [Start Date Formula], [End Date Formula]) + 1 - 2 * DATEDIFF('week', [Start Date Formula], [End Date Formula]) )
Additionally, you can also compute number of weeks using the step 4
Step 4: Number of weeks
To get number of weeks between the range of dates, divide it 5 as, as we have 5 weekdays in a week
[Number of days]/5