Date subtraction formula referencing another date formula - inconsistent results

[Former Member]

I have the following two formulas in a report.  The first one works perfectly, but the second one produces inconsistent results.

The same holiday formula is in the report header so it is used for both formulas.

I am working on an older version of Crystal, Crystal 2008.

Can someone help me determine why the second formula would produce inconsistent results?  Some dates calculated in the second formula are correct, but others are off by a day.

Formula 1:  CD Mail Date Formula:

Note:  This formula subtracts days from a date field.

The formula is named 'CD Mail Date'

//Subtracting Business Days:

//Subtracts 7 days from Anticipiated Closing Date. Excludes Sundays and Holidays

WhileReadingRecords;

DateVar Array Holidays;

DateVar Target:= DATE({TRACKING_FILE.f420#anticip_closing_date});  //Put your field name in here

NumberVar Add:= -6; // Put in the number of days to subtract (a negative number)

NumberVar Added := 0;

WHILE Added > Add

Do (target := target -1;

     if dayofweek (target) in 2 to 7 and not (target in holidays)

     then Added:=Added-1

     else Added:=Added);

Target

Formula 2:  Deliver to Closing Date:

Note:  This formula subtracts days from the results of the first formula above.

//Subtracting Business Days:

//Subtracts 3 days from calculated CD mail date. Excludes Saturdays, Sundays and Holidays

WhileReadingRecords;

DateVar Array Holidays;

DateVar Target:= {@CD Mail Date};  //Put your field name in here

NumberVar Add:= -3; // Put in the number of days to subtract (a negative number)

NumberVar Added := 0;

WHILE
Added > Add

Do (target := target -1;

       if dayofweek (target) in 2 to 5 and not (target in holidays)

      then Added:=Added-1

      else Added:=Added);

Target

[former_member292966 · Accepted solution]

Hi Diann,

When you use WhilePrintingRecords there are two things you need to be aware of: 

1)  Crystal evaluates formulas Left to Right and Top Down.  So Formula 2 must always be to the right of Formula 1 or in a section below. 

2)  Be cautious of when the variables are being set between the formulas. 

I noticed the variable called Target is being set in both formula.  You shouldn't have to reset it in Formula 2.  You just need to define it and it will already get it from Formula 1. 

Try changing it like: 

//Subtracting Business Days:

//Subtracts 3 days from calculated CD mail date. Excludes Saturdays, Sundays and Holidays

WhileReadingRecords;

DateVar Array Holidays;

DateVar Target;  //Put your field name in here

NumberVar Add:= -3; // Put in the number of days to subtract (a negative number)

NumberVar Added := 0;

WHILE
Added > Add

Do (target := target -1;

       if dayofweek (target) in 2 to 5 and not (target in holidays)

      then Added:=Added-1

      else Added:=Added);

Target

Where are you populating the Holidays array?  I suspect in another formula and it's being passed to both of these two.  Again, make sure the Holidays array is being populated before these 2 formulas are being populated. 

Good luck,

Brian

[Former Member · Accepted solution]

Your suggestions were helpful.

Also, I had day of week in 2 to 5 and it should have been 2 to 6.

Thanks.