Jump to content

Remove Weekend From DateDiff('day', [Order Date], [Delivery Date])

Tanveer Rehman

Recommended Posts

The DateDiff function returns a value for the time difference, not any date.

Please explore the DatePart() function. There you could useDatePart('dw',[Order Date]) orDatePart('dw',[Delivery Date]) to check if the day of the week 'dw' of the order or delivery date is a Saturday (=6) or Sunday (=7)

Link to comment
Share on other sites

After searching and exploring different topic/analysys Following expression worked for me to calculate working days . now i just need to figureout how to remove holidays ...


Explanation of expression:


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



Replace the START and END DATES with your corresponding columns.


We are using following expression which calculate days

DateDiff("dd",[sTART DATE],[END DATE])




Below mentioned expression is to calculate working days between two dates in Spotfire.

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]))

Link to comment
Share on other sites

Now I fully understand your initial request...

You could solve this with an expression function. This requires a little bit of R experience. Please check the Spotfire help to understand how expression functions work. Then you can use the following function. Modify the vector with the holidays.:


Link to comment
Share on other sites

  • 2 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...