1999-2000 Calculation problem

[Sunwolves]

Look at this formula for calculating a disabilty pay rate for injured workers:

Case(DayofYear(Date of Injury) <= DayofYear(Date(10,31,1999)) and Average Weekly Wage * .7 <= 426.,Average Weekly Wage * .7 ,DayofYear(Date of Injury) <= DayofYear(Date(10,31,1999)) and Average Weekly Wage * .7 >= 426.,426.,DayofYear(Date of Injury) >= DayofYear(Date(11,1,1999)) and Average Weekly Wage * .7 <= 473.,Average Weekly Wage * .7,DayofYear(Date of Injury) >= DayofYear(Date(11,1,1999)) and Average Weekly Wage * .7 >= 473.,473.)

Can somebody tell me why if the Date of Injury is 12/31/1999 and the injured worker's Average Weekly Wage was more than $473, the calculation works and gives me a answer of $473.00. But if the date of injury is 1/1/00 or after, it only gives me an answer of $426.00. The calculation works beautifully unless they got hurt in 2000 or 2001! And yes, of course, my users are entering the year part of date as a four digit year. Can someone please tell me how to fix this?

[BobWeaver]

I think you should probably leave out the DayOfYear() function. Then, your calculation becomes:

Case(

Date of Injury <= Date(10,31,1999) and Average Weekly Wage * .7 <= 426.,Average Weekly Wage * .7 ,

Date of Injury <= Date(10,31,1999) and Average Weekly Wage * .7 >= 426.,426.,

Date of Injury >= Date(11,1,1999) and Average Weekly Wage * .7 <= 473.,Average Weekly Wage * .7,

Date of Injury >= Date(11,1,1999) and Average Weekly Wage * .7 >= 473.,473.)

[LeCates]

Bob is right, the DayOfYear() function is creating the error.

DayOfYear() for Jan 1, will be equal to 1 no matter what year you provide. And thus that will always be less than DayOfYear(Date(10,31,1999)) so the answer will be restricted to the $426 result.

Note that the calcuation will also break prior to 10/31/1999, for dates in November and December. Hence, 11/15/1998 will result as if the date were > 11/1/1999.

Also, make sure that you handle cases where the date will be empty if an empty date is possible, since a null date compared to 10/31/1999 will product a null result (then the Case statement can't work either).

HTH. Good luck.