1 2 Previous Next 26 Replies Latest reply on Apr 16, 2019 2:45 PM by Mike Kiel Go to original post
• ###### 15. Re: DateDiff for Working days only

Hi All,

I have found a way to exclude weekends and have only working days between two days.

1. Create a set of date [Order date] where write a formula condition:

DATENAME('weekday',[Order Date])!="Saturday"

and

DATENAME('weekday',[Order Date])!="Sunday"

2. Use the set to create a calculated field "calculated date" :

if [Set 1] THEN [Order Date] END

It will create a calculated calendar field where weekends are not present.

3.Create to parameter "From Date" & "To Date".

4. Another calculated field

countd(if dateturnc('day',"From Date" >)= dateturnc('day',"Calculated date") and dateturnc('day',"To Date" )

<= dateturnc('day',"Calculated date" >) then "Calculated date" end)

it will serve the purpose.

Thanks,

Chandra Shekhar

• ###### 16. Re: DateDiff for Working days only

Just a thought, would the below formula work?

DATEDIFF( 'weekday', [Start Date], [Finish Date], 'monday') - (DATEDIFF( 'week', [Start Date], [Finish Date], 'monday') * 2 )

• ###### 17. Re: DateDiff for Working days only

Hello,

I am trying to use this formula to calculate the Number of Days between two dates, either positive or negative.

It doesn't work for the case below, - I am calculating the difference between the Statistic Delivery Date and the posting date

In the line 20 it should factually be -1, as the posting date was made earlier,  but it comes up at 4..

Any Ideas?

thanks

Benjamin

• ###### 18. Re: DateDiff for Working days only

The formula requires creation of 3 Calculated fields: Weekday Begin, Weekday Closed, and Networkdays

[WEEKDAY BEGIN]

//Determine the day of the week that your process began

datepart('weekday',[Process Begin Date],'Sunday')

[WEEKDAY CLOSED]

//Determine the day of the week that your process closed

datepart('weekday',[Process Close Date],'Sunday')

[NETWORKDAYS]

//Calculate total days for full weeks past, relative to the Process Begin Date

int(([Process Close Date]-[Process Begin Date]-1)/7) * 5 +

//Calculate remainder days (over and above full weeks), relative to the day of the week that your process began

case [Weekday Begin]

when 1 then

if [Weekday Closed]=1 then 0

elseif [Weekday Closed]=7 then 4

else [Weekday Closed]-([Weekday Begin]+1) end

when 2 then

if [Weekday Closed]=1 or [Weekday Closed]=7 then 4

else [Weekday Closed]-[Weekday Begin] end

when 3 then

if [Weekday Closed]=1 or [Weekday Closed]=7 then 3

elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

else [Weekday Closed]-[Weekday Begin]+5 end

when 4 then

if [Weekday Closed]=1 or [Weekday Closed]=7 then 2

elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

else [Weekday Closed]-[Weekday Begin]+5 end

when 5 then

if [Weekday Closed]=1 or [Weekday Closed]=7 then 1

elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

else [Weekday Closed]-[Weekday Begin]+5 end

when 6 then

if [Weekday Closed]=1 or [Weekday Closed]=7 then 0

elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

else [Weekday Closed]-[Weekday Begin]+5 end

when 7 then

if [Weekday Closed]=1 or [Weekday Closed]=7 then 0

else [Weekday Closed]-[Weekday Begin]+5 end

end

• ###### 20. Re: DateDiff for Working days only

I found it cleaner to set monday to the start of the week so that the weekdays are 1 - 5

[Todays Day Number]

date(NOW())-datetrunc('week',date(NOW()),'Monday')+1

[Last Day of Month Day Number] //date dimension includes all dates from the current month

date(max([date]))-datetrunc('week',max([date]),'Monday')+1

// (1) Calculate number of whole weeks between dates, then convert to # of Work Days

INT((MAX([date])-NOW())/7)*5

+

// (3) Add # of Work Days for last week

if [Last Day of Month Day Number] >= [Todays Day Number] //start and end is in the same week (mon to sun)

THEN

IF [Last Day of Month Day Number] = 7 //if ends on sunday removes an extra day

then max([Last Day of Month Day Number] - [Todays Day Number] - 1,0)

ELSEIF [Last Day of Month Day Number] = 6

then [Last Day of Month Day Number] - [Todays Day Number]

ELSE [Last Day of Month Day Number] - [Todays Day Number] + 1

end

ELSE //if the start and end date fall into separte weeks need to break up the weeks in two

//the first section treat 7 as the last day

max(7 - [Todays Day Number] - 1,0)

+

//the second section treats the start date as 1 so its just the day number or 5, whichever is less

min([Last Day of Month Day Number],5)

end

1 of 1 people found this helpful
• ###### 21. Re: DateDiff for Working days only

This one works OK, the only problem is when both dates are on the same week and the end date is Saturday or Sunday.

I solved it like this. ID-Initial Date FD-Final Date

In BOLD is the original formula, added only some conditions for the case when both dates are on the same week.

IF DATEPART('week', [FD],'monday')<>DATEPART('week', [ID],'monday') THEN

DATEDIFF("weekday",[ID],[FD],'monday')-2*(DATEPART('week', [FD],'monday') -DATEPART('week', [ID],'monday'))

ELSEIF DATEPART('day', [FD],'monday')=6 THEN

DATEDIFF("weekday",[ID],[FD],'monday')-1

ELSEIF DATEPART('day', [FD],'monday')=7 THEN

DATEDIFF("weekday",[ID],[FD],'monday')-2

ELSE

DATEDIFF("weekday",[ID],[FD],'monday')

END

• ###### 22. Re: DateDiff for Working days only

Thanks for posting this Lynn and Alex. This will be very useful!

• ###### 24. Re: DateDiff for Working days only

Thank you Shin!

• ###### 25. Re: DateDiff for Working days only

You are welcome.

Shin

• ###### 26. Re: DateDiff for Working days only

Sina,

When I have issues with blending, I use a left join on the two data sources.

Hope this helps (7 years later.....)

1 2 Previous Next