How to calculate working days between two dates in Spotfire

How to calculate working days between two dates in Spotfire

This article explains how to calculate working days between two dates in TIBCO Spotfire using a Spotfire expression. Use the following expression to calculate working days between two dates in Spotfire. Replace the START and END DATES with your corresponding columns. Note that this formula does not take into account national holidays.

if(dayofyear([START DATE])=dayofyear([END DATE]),0,(Integer(DateDiff("day",[START DATE],[END DATE]) / 7) * 5) + 
DayOfWeek([END DATE]) - DayOfWeek([START DATE]) + (if((DayOfWeek([END DATE]) - DayOfWeek([START DATE]))<0,5,0)))

If you would like to include start and end dates in the count of working days, you can use below custom expression:

DATEDIFF("day",[START DATE],[END DATE])
- (2 * (DATEPART("week",[END DATE]) - DATEPART("week",[START DATE])))
+ (CASE WHEN (DayOfWeek([END DATE])=6) AND (DayOfWeek([START DATE])=0) THEN -1 
         WHEN (DayOfWeek([END DATE])=6) OR (DayOfWeek([START DATE])=0) THEN 
     0 ELSE 1 END)

Explanation:

• DATEDIFF("day",[StartDate],[EndDate]) = no of days between start and end date
• DATEPART("week",[EndDate]) - DATEPART("week",[StartDate]) = no of weeks between start and end date multiplied by 2 to get no of Saturdays and Sundays
• CASE clause = subtract 1 when end date is Saturday and start date is Sunday, add 1 when end date is not Saturday and start date is not Sunday

See the attached file NoOfWorkingDays.dxp for reference Doc: Functions Overview

• https://docs.tibco.com/pub/sfire-analyst/10.3.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_functions_overview.htm