Calculating the Number of Business Days

Summary

Count the number of Business Days between 2 dates

Content

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