Solved

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

Posted on 2011-05-05
1,345 Views
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!
0
Question by:J C

LVL 77

Expert Comment

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

0

LVL 4

Expert Comment

can you elaborate your requirements ?
0

LVL 19

Expert Comment

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]

//@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

HTH
0

LVL 19

Accepted Solution

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

0

Author Comment

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

LVL 100

Expert Comment

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

Author Comment

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

Author Comment

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

LVL 100

Expert Comment

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

Author Comment

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

Author Comment

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

Author Comment

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

LVL 19

Expert Comment

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

Author Comment

HTH,

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

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

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

## Featured Post

How to increase the row limit in Jasper Server.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦