PDA

View Full Version : datediff excluding weekends



wstansbury
27-Mar-2013, 11:43 AM
Could you please help me modify the following formula to exclude weekends

datediff (d,date1,date2)

kelly
27-Mar-2013, 11:57 AM
Dates.nui contains a lot of useful functions for handling dates in many ways.

Not sure it helps in your case but I thought to mention it in case it might help in the future.

Larry R Pint
27-Mar-2013, 12:00 PM
Maybe like this:



Function MyDateDiff Date dFromDate Date dToDate returns integer
Date dTempDate
Integer iCount iDay

if (dToDate < dFromDate) function_Return -1

move 0 to icount
move dFromDate to dTempDate

repeat
move (DateGetDayOfWeek(dTempDate)) to iDay
if ((iDay <> 1) and (iDay <> 7)) increment icount
move (dTempDate + 1) to dTempDate
until (dTempDate > dToDate)

function_Return iCount
end_Function



Might be slow if the dates are very far apart.

kelly
27-Mar-2013, 12:02 PM
Maybe like this:

[CODE]
Function MyDateDiff Date dFromDate Date dToDate returns integer
Date dTempDate
Integer iCount iDay

if (dToDate < dFromDate) function_Return -1

move 0 to icount
move dFromDate to dTempDate

repeat
move (DateGetDayOfWeek(dTempDate)) to iDay
if ((Iday <> 1) and (iDay <> 7)) incrmeent icount
move 9dTempDate + 1) to dTempDate
until (dTempDate > dToDate)

function_Return iCount
end_Function
[\CODE]

Might be slow if the dates are very far apart.

Just for reference and I'm not picking your code to bits Larry - increment is spelt wrong.

Larry R Pint
27-Mar-2013, 12:05 PM
Yes, I caught that and fixed it. Thanks.

kelly
27-Mar-2013, 12:18 PM
I just noticed this thread is in the forum q & a.

For your information Qzn's of product nature should in future be in the appropriate forum i.e in this case Visual Dataflex.

Stephen W. Meeley
27-Mar-2013, 12:19 PM
I moved this thread.

kelly
27-Mar-2013, 12:21 PM
Thanks Stephen, I was hoping you would.

wstansbury
27-Mar-2013, 12:39 PM
Thanks all for your help but where would I put this in Dynamic AI?

Bob Cergol
27-Mar-2013, 09:44 PM
Hi Wayne,

You somehow posted on the VDF forum instead of the Dynamic AI forum.

Anyway I think the answer to your question is pretty simple, no fancy programming, no repeat/until loops -- but let me restate your question.

Question: How do I calculate the number of workdays within a date range and show it in a report?

Answer: You just create an extra column in your report using the following formula:


(DATEDIFF(dd, StartDate, EndDate) + 1)
-(DATEDIFF(wk, StartDate, EndDate) * 2)
-(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END)

So let's say I have an Order table containing a date entered and a date shipped and you want to know the number of work days it took from booking the job to shipping the job. You'd create the extra formula column, name it whatever you like, specify data type of integer, and replace the start and end date with the appropriate data column names in your data source like this:

63066305

If you will be using this formula in many reports, you could define it as a Global SQL Formula in the data dictionary of your data source in Dynamic AI.

CAVEAT: This formula relies on a peculiarity in the DateDiff function in MS-SQL that may not be true with an equivalent date function in other SQL backends. In MS-SQL the number of weeks in a date range returned by the wk argument in the function actually counts the number of full weekends (Saturday + Sunday) in the date range -- hence the case statements to deal with case when the date range starts on a Sunday or ends on a Saturday.

Regards,
Bob

Michael Mullan
28-Mar-2013, 12:07 PM
That's a neat trick.. I like it.

kelly
2-Apr-2013, 06:27 PM
It wasn't clear from your original post which product you were referring to which is why I suggested it be moved to Visual DataFlex.

Yes if it is for Dynamic AI it needs to be in Dynamic AI.