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

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

http://www.kenhamady.com/form01.shtml

can you elaborate your requirements ?

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]

Select allOpen 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

Select allOpen in new window

HTH

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.

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

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?

Can someone provide a formula for the weekend days as well?

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

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?

The above does not return the desired result. I am not sure where to go from here so any feedback is appreciated.

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!

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

HTH,

Thanks for sticking with me. That's exactly what I needed. Thank you very much!

jleecole - FYI - HTH is short for Happy To Help not his name or initials

mlmcc

Yeah it was 2am when I posted that I think, was pretty out of it. Thanks for your contributions as well.