?
Solved

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

Posted on 2011-05-05
16
Medium Priority
?
1,466 Views
Last Modified: 2012-06-22
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
Comment
Question by:J C
  • 8
  • 3
  • 3
  • +2
16 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35704440
To calculate working days between 2 dates use the method and formula from this link..

http://www.kenhamady.com/form01.shtml
0
 
LVL 4

Expert Comment

by:musalman
ID: 35704880
can you elaborate your requirements ?
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35706496
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
GJParker earned 2000 total points
ID: 35706512
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
 

Author Comment

by:J C
ID: 35707809
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 101

Expert Comment

by:mlmcc
ID: 35708100
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

by:J C
ID: 35740006
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

by:J C
ID: 35740062
Can someone provide a formula for the weekend days as well?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35740508
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

by:J C
ID: 35742621
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

by:J C
ID: 35743103
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

by:J C
ID: 35743121
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

by:GJParker
ID: 35744868
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

by:J C
ID: 35744949
HTH,

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

Expert Comment

by:mlmcc
ID: 35747973
jleecole - FYI - HTH is short for Happy To Help not his name or initials

mlmcc
0
 

Author Comment

by:J C
ID: 35748552
Yeah it was 2am when I posted that I think, was pretty out of it. Thanks for your contributions as well.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question