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

[Tanveer Rehman]

i would like to return working days when i utiliseDateDiff function.

[Fabian Duerr]

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)

[Tanveer Rehman]

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:

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

 

 

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

[Fabian Duerr]

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.:

WorkDays

[Tanveer Rehman]

curiously been trying it but couldn't figure out. i must be doing something wrong.