Print Page | Close Window

Calculate Bus Days

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22781
Printed Date: 26 Apr 2024 at 12:30am


Topic: Calculate Bus Days
Posted By: BoltzGirl
Subject: Calculate Bus Days
Date Posted: 28 May 2019 at 12:44pm
I want this formula to account for business days when doing the count of <=3 days. How do I do that...my mind drew a big blank today!

If {HSD_AUTH_MASTER.REVIEW_USER_DEFINED_DATE} - {HSD_AUTH_MASTER.DISCH_THRU_DATE} <=3
then '1'
Else '0'

Thank you in advance!!

-------------
Always appreciate the help!


Replies:
Posted By: kevlray
Date Posted: 29 May 2019 at 9:58am
Normally I would use the datediff function.Something like this

if datediff("d", {HSD_AUTH_MASTER.DISCH_THRU_DATE}, {HSD_AUTH_MASTER.REVIEW_USER_DEFINED_DATE} <= 3 then '1' else '0'

Even though subtracting the two dates should work.

Posted By: BoltzGirl
Date Posted: 30 May 2019 at 1:16pm
I am sorry to be a totally bug on this, but is it the "d" in that formula that then accounts for the weekdays? I just want to understand how that works as I saw another one done with "dd"....

-------------
Always appreciate the help!

Posted By: kevlray
Date Posted: 31 May 2019 at 9:19am
I checked the help files, and I did not find any information on "dd". There is an example in the help files.

For example, suppose you want to calculate the number of days between the order date and ship date, excluding Saturdays and Sundays: 

Rem Basic syntax
Dim d1, d2
d1 = {Orders.Order Date}
d2 = {Orders.Ship Date}
formula = DateDiff("d", d1, d2) - _
            DateDiff("ww", d1, d2, crSaturday) - _
            DateDiff("ww", d1, d2, crSunday)

//Crystal syntax
Local DateTimeVar d1 := {Orders.Order Date};
Local DateTimeVar d2 := {Orders.Ship Date};
DateDiff ("d", d1, d2) - 
     DateDiff ("ww", d1, d2, crSaturday) -
     DateDiff ("ww", d1, d2, crSunday)

Posted By: BoltzGirl
Date Posted: 31 May 2019 at 10:38am
Awwww....

That is such great information. So for mine specific need right now, if I want to exclude Weekends and Holidays from my formula; I think I would still need to build a formula for our observed Holidays and somehow fit it into this right?

So the "d" is excluding what exactly?

if datediff("d", {HSD_AUTH_MASTER.DISCH_THRU_DATE}, {HSD_AUTH_MASTER.REVIEW_USER_DEFINED_DATE} <= 3 then '1' else '0'

If I wanted to exclude Sat/Sun, would I change the "d" to be more like the "ww"?

-------------
Always appreciate the help!

Posted By: kevlray
Date Posted: 31 May 2019 at 12:54pm
I never had to count days excluding weekends, so I am not sure on that.  On Ken Hammady's web site.  He has a formula to identify holidays.  Of course what constitutes a holiday could be different.  I took the formula modified it for our organization (we get Christmas eve off, unless it falls on a weekend, then other rules apply).


Print Page | Close Window