Need formula for Crystal Reports aging report that will age based on Working days rather then calendar days

I am needing a formula for a Crystal Reports aging report that will age based on Working days rather then calendar days

My current formula's look like this.

if CurrentDate-{WOHeader.RequestDate} > 14 and CurrentDate-{WOHeader.RequestDate} <= 30 then 1 else 0

Any help is appreciated!
J CAsked:
Who is Participating?
 
GJParkerConnect With a Mentor Commented:
Here is teh amended formula to use alongside the above formulas

@DaysDifference
Global DateVar Array Holidays;
Local NumberVar FullDays;
Local NumberVar BankHolidays;
Local NumberVar Weekends;
Local NumberVar i;
Local Datevar StartDate := //put your date here;
Local DateVar EndDate := Today;

    (
    //Count the number of full days between dates
    Fulldays := DateDiff ('d',StartDate,EndDate);

    //Count any holidays between dates
    For i := 1 to Ubound(holidays)
    Do
        If Holidays[i] >= StartDate and Holidays[i] <= EndDate Then
            BankHolidays := BankHolidays + 1;

    //Count any weekends between dates
    Weekends := DateDiff("ww", StartDate, EndDate, crsaturday) + 
                DateDiff("ww", StartDate, EndDate, crsunday)
    );

//Calculate Working days
FullDays - Weekends - BankHolidays

Open in new window

0
 
peter57rCommented:
To calculate working days between 2 dates use the method and formula from this link..

http://www.kenhamady.com/form01.shtml
0
 
musalmanERP ConsultantCommented:
can you elaborate your requirements ?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
GJParkerCommented:
Alongside the link given by peter57r I also use the following formulas to automatically calculate the holidays, you will need to alter the @Holidays formual to include your own regional holidays

//@Holidays
Local numberVar yr := Year(Today); 
Global DateVar Array holidays;
    redim holidays [11];

holidays [1] := CDate (yr-1, 12, 25);//Christmas day last year
holidays [2] := CDate (yr-1, 12, 26); //Boxing day last year
holidays [3] := CDate (yr, 1, 1); // New Years day
holidays [4] := {@GoodFriday}; // Good Friday
holidays [5] := {@GoodFriday} + 3; //Easter Monday
holidays [6] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 5, 1) , crTuesday), CDate (yr, 5, 1) ) );
                                        // May Bank Holiday (First Monday in May)
holidays [7] := CDate (DateAdd ("d", 1 - DayOfWeek (CDate (yr, 5, 31), crMonday), CDate (yr, 5, 31) ) );
                                        // Spring Bank Holiday (last Monday in May)
holidays [8] := CDate (DateAdd ("d", 1 - DayOfWeek (CDate (yr, 8, 31) , crMonday), CDate (yr, 8, 31) ) );
                                      // Summer Bank Holiday (last Monday in Sept)
holidays [9] := CDate (yr, 12, 25);   // Christmas day
holidays [10] := CDate (yr, 12, 26); // Boxing day
holidays [11] := Cdate (yr+1,1,1); //New years day next year

// check for holidays that fall on weekend: New Years, Christmas and Boxing day

if DayOfWeek (holidays [11]) = crSunday then
    holidays [11] := CDate (DateAdd ("d", 1, holidays [11]))     // New Years Day Next year
else if DayOfWeek (holidays [11]) = crSaturday then
    holidays [11] := CDate (DateAdd ("d", 2, holidays [11]));

if DayOfWeek (holidays [1]) = crSunday then
    holidays [1] := CDate (DateAdd ("d", 1, holidays [1]))     // Christmas Day Last year
else if DayOfWeek (holidays [1]) = crSaturday then
    holidays [1] := CDate (DateAdd ("d", 2, holidays [1]));

if DayOfWeek (holidays [2]) = crSunday or DayOfWeek (holidays [2]) = crSaturday then
    holidays [2] := CDate (DateAdd ("d", 2, holidays [2]));     // Boxing Day last year

if DayOfWeek (holidays [3]) = crSunday then
    holidays [3] := CDate (DateAdd ("d", 1, holidays [3]))     // New Years Day
else if DayOfWeek (holidays [3]) = crSaturday then
    holidays [3] := CDate (DateAdd ("d", 2, holidays [3]));

if DayOfWeek (holidays [9]) = crSunday then
    holidays [9] := CDate (DateAdd ("d", 1, holidays [9]))     // Christmas Day
else if DayOfWeek (holidays [9]) = crSaturday then
    holidays [9] := CDate (DateAdd ("d", 2, holidays [9]));

if DayOfWeek (holidays [10]) = crSunday or DayOfWeek (holidays [10]) = crSaturday then
    holidays [10] := CDate (DateAdd ("d", 2, holidays [10]));     // Boxing Day


holidays [1] & chr(13) & 
holidays [2] & chr(13) & 
holidays [3] & chr(13) & 
holidays [4] & chr(13) & 
holidays [5] & chr(13) & 
holidays [6] & chr(13) & 
holidays [7] & chr(13) & 
holidays [8] & chr(13) & 
holidays [9] & chr(13) & 
holidays [10] & chr(13) & 
holidays [11]

Open in new window


//@GoodFriday
Local numberVar yr := {@ThisYear};  // The year for which to determine the date of Good Friday.
    Local numberVar a := yr Mod 19;     
    Local numberVar b := yr \ 100;    
    Local numberVar c := yr Mod 100;  
    Local numberVar d := b \ 4;
    Local numberVar e := b Mod 4;     
    Local numberVar i := c \ 4;
    Local numberVar k := c Mod 4;
    Local numberVar g := (8 * b + 13) \ 25;
    Local numberVar h := ((19 * a) + b - d - g + 15) Mod 30;
    Local numberVar l := ((2 * e) + (2 * i) - k + 32 - h) Mod 7;
    Local numberVar m := (a + (11*h) + (19*l)) \ 433;
    Local numberVar days_to_good_friday := h + l - (7*m) - 2; 
    Local numberVar mo := (days_to_good_friday + 90) \ 25;
    Local numberVar da := (days_to_good_friday + (33 * mo) + 19) Mod 32;
    cdate ( yr, mo, da)     // Returns the date of Good Friday

Open in new window


HTH
0
 
J CAuthor Commented:
This is a Work Order aging report. Paramaters are not entered so there are no start/end date variables that are fed into the query. The query looks at all of the open Work Order's that are in the system and if they are 30 days and under the number of Work Orders for each vendor are supplied via the formula I posted. We also go past 30-60 and so on. The problem is, it isn't entirely accurate with counting all calendar days against them when there are 5 working days in a week and then holidays as well. Hope this helps.
0
 
mlmccCommented:
The formulas above will calculate what you want.

How "old" might a workorder be?

You need to include holidays back as far as necessary and as far into the future as you think is necessary.

You could set the holidays in a database table and populate the array in a subreport in the report header.  

What problem do you have with the formulas above?

mlmcc
0
 
J CAuthor Commented:
I was providing more information as to what I was trying to do, it was requested by musalman. I don't see any problem with the formula's above but I am not sure exactly how to tie it all together. Can someone give me an example of what the overall formula would look like including the one I posted here?
0
 
J CAuthor Commented:
Can someone provide a formula for the weekend days as well?
0
 
mlmccCommented:
The formula accounts for weekends.  It is set for Saturday and Sunday as the weekend but it could be changed  be changed to work for any 2 days.

Put GJParkers 3 formula in your report.

mlmcc
0
 
J CAuthor Commented:
I did add the formulas. Should this be the way the formula works?

if CurrentDate-{WOHeader.RequestDate} > 14 - {@DaysDifference} {and CurrentDate-{WOHeader.RequestDate} <= 30 then 1 else 0

where I am subtracting {@DaysDifference} from the equasion?
0
 
J CAuthor Commented:
The above does not return the desired result. I am not sure where to go from here so any feedback is appreciated.
0
 
J CAuthor Commented:
On the report, I have 5 different formulas. ZeroTo14, LessThan30, LessThan60 and so on. The formula above is LessThan30. I need for the formula above to check the WOHeader.RequestDate and return the number of work orders that are between 15 and 30 days based on number of Business Days versus Calendar days. I think you've given me what I need, I am just not sure how to tweak each of the existing formulas to give the desired result.

The one change I made to the DaysDifference formula that was provided is that I set the StartDate to {WOHeader.RequestDate}

Thanks!

0
 
GJParkerCommented:
The DaysDifference formula is calculating the number of working days between {WOHeader.RequestDate}
 and Today so your range formulas should now be like

if {@DaysDifference} > 14 and {@DaysDifference} <= 30 then 1 else 0

HTH



0
 
J CAuthor Commented:
HTH,

Thanks for sticking with me. That's exactly what I needed. Thank you very much!
0
 
mlmccCommented:
jleecole - FYI - HTH is short for Happy To Help not his name or initials

mlmcc
0
 
J CAuthor Commented:
Yeah it was 2am when I posted that I think, was pretty out of it. Thanks for your contributions as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.