| Author |
Message |
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
   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 Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
|
|
