Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: Calculate Bus Days Post Reply Post New Topic
Author Message
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet Topic: Calculate Bus Days
    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!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.


Edited by kevlray - 29 May 2019 at 9:59am
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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)
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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).
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.