
15. Re: DateDiff for Working days only
chandra shekhar banerjee Aug 8, 2016 10:06 AM (in response to sinadashtipour)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
Tim Weber Aug 30, 2017 12:41 AM (in response to sinadashtipour)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
Benjamin Rubattel Apr 24, 2018 2:24 AM (in response to Lynn Snow)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
Robin Foster Apr 27, 2018 11:38 AM (in response to sinadashtipour)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
James Rizkallah Aug 29, 2018 11:22 AM (in response to sinadashtipour)1 of 1 people found this helpfulI 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

21. Re: DateDiff for Working days only
DANIEL GUZMAN Mar 22, 2019 2:46 PM (in response to sinadashtipour)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. IDInitial Date FDFinal 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

Dif_Fechas_sinFinDeSemana.twbx 51.5 KB


22. Re: DateDiff for Working days only
Mike Kiel Apr 16, 2019 12:08 PM (in response to Alex Kerin)Thanks for posting this Lynn and Alex. This will be very useful!

23. Re: DateDiff for Working days only
Shinichiro Murakami Apr 16, 2019 1:19 PM (in response to Mike Kiel) 
24. Re: DateDiff for Working days only
Mike Kiel Apr 16, 2019 2:02 PM (in response to Shinichiro Murakami)Thank you Shin!

25. Re: DateDiff for Working days only
Shinichiro Murakami Apr 16, 2019 2:08 PM (in response to Mike Kiel)You are welcome.
Shin

26. Re: DateDiff for Working days only
Mike Kiel Apr 16, 2019 2:45 PM (in response to sinadashtipour)Sina,
When I have issues with blending, I use a left join on the two data sources.
Hope this helps (7 years later.....)

27. Re: DateDiff for Working days only
PlanOmatic Admin Oct 4, 2019 12:47 PM (in response to sinadashtipour)This thread had the answer I was looking for related to this topic: Re: Number of weekend days between two dates (or business days)

28. Re: DateDiff for Working days only
Joseph Hall Nov 4, 2019 12:20 PM (in response to sinadashtipour)I use the following to calculate workdays (does not address holidays):
// Calculate work days  5day workweek (change the 2 to a 1 for 6day workweek)
([Completed Date]  [Created Date])  ((DATEDIFF('week',[Created Date],[Completed Date])) * 2)