Calculate Date Diff excluding Weekends

[peterempson]

Is there a function within Crystal Reports to calculate to difference in days between two dates and exclude weekends in the calculation?

Thanks in advance!

 

[smcnulty2000]

I don't know of a function, per se.

Here is a partial formula solution.


datediff("d", {@Start Date},{@End Date})
-(
(datediff("ww",{@Start Date},{@End Date}))
)*2


The 'ww' asks for how many week starts there were between the two dates.

It works great if neither the start date nor the end date occur on a saturday. I don't know whether your data hits the weekends. If it does, you may want to set up an if-then because all it will need is for you to push the real date backward one (for the end date) or forward one (for the start date).

If I come up with anything better, I'll drop it off here.

Scott.

 

[smcnulty2000]

Okay, here's the better formula.


datediff("d", {@Start Date},{@End Date})
-
(
(datediff("ww",{@Start Date},{@End Date}))
+
(datediff("ww",{@Start Date},{@End Date},7))
)


I had a 'doh!' moment right after posting. One of the'ww' checkers just had to be moved to see how many saturdays were between the two dates. The default is sundays.

BTW, this won't do a thing for holidays.

Scott.

 

[chelseatech]

The Cr help files suggest you use this formula (where d1 and d2 are your two date fields)....

DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday)




Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[smcnulty2000]

You know I looked for this in my help file and couldn't find it. What version and what search string gets you there?

Scott.