http://www.kenhamady.com/f

Solved

Posted on 2011-05-05

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!

My current formula's look like this.

if CurrentDate-{WOHeader.Requ

Any help is appreciated!

16 Comments

http://www.kenhamady.com/f

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

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

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

Put GJParkers 3 formula in your report.

mlmcc

if CurrentDate-{WOHeader.Requ

where I am subtracting {@DaysDifference} from the equasion?

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

Thanks!

By clicking you are agreeing to Experts Exchange's Terms of Use.

How to increase the row limit in Jasper Server.

This video discusses moving either the default database or any database to a new volume.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**21** Experts available now in Live!