For more information, please refer to this announcement explaining best practices for getting answers to questions.
Calculating the Number of Business Days
Summary
Count the number of Business Days between 2 datesContent
I constantly get requests to provide reporting based on Business Days rather than Calendar Days. Calculating the difference between 2 Calendar dates is easy:
This example shows 2 days:
Created Date
Closed Date
TIMESTAMPDIFF(SQL_TSI_DAY, "Time"."Created Date", "Time"."Closed Date")
No big deal!
But what if your challenging user asks to exclude weekends (ie Business Days), not so easy. Here’s the magical code that will make you a hero:
-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."Closed Date")-1),"Time"."Closed Date"), TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Created Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Created Date")))/7*5+ MOD(7-DAYOFWEEK("Time"."Created Date"),6)+CASE WHEN DAYOFWEEK("Time"."Closed Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."Closed Date")-2 END