asked on

Two year comparision isn't working because of leap year in 2008

I have a sales report that compares this year to last year by month. I'm using a data range and if I select 2/1/2008 to 2/29/2008, I'm getting this error message: A day number must be between 1 and the number of days in the month. We will be invoicing on the 29th and need to include that date to last year, even though there were 28 days last year in Feb. How do I fix this?

mnye

The datetime data type wont let you enter invalid dates. You should write in exception statements for leap years:

If {@leapYear} = true Then
//Code to compare March 1st PY to Feb 29th CY
End

sfrisk

ASKER

I don't want to compare March 1st to Feb. 29th - it would just be an extra day in February.

How would I write that?

mnye

for all calculation do the same as above and convert your parameter values to formulas. Create two formulas. One StartDate and one EndDate then use those formulas instead of the parameters through out your report.

//StartDate formula
 
If Day({?StartDate}) = 29 AND Month({?StartDate}) = 2  Then
Date(Year({?StartDate}, 02, 28)
Else
{?StartDate}

Open in new window

mnye

Sorry forgot to mention, you need to use the Leap Year mod. Its a true false designator, so add that as an AND clause in the above formulas. I would create this as a custom formula

if Year({?StartDate}) mod 400 = 0 then true
 else if Year({?StartDate}) mod 100 = 0 then false
 else if Year({?StartDate}) mod 4 = 0 then true
 else false

Open in new window

sfrisk

ASKER

mnye:

Just getting back on this - do I use both of the formulas you posted, or just the last one?

Thanks.

ASKER CERTIFIED SOLUTION

mnye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mnye

Opps, the above code was slightly off

If IsLeapYear(Year({?StartDate})) then

should be

If IsLeapYear(Year({?StartDate})) = true then

The syntax for the Custom Formula would be:

function (numbervar argYear)
if argYear mod 400 = 0 then true
 else if argYear mod 100 = 0 then false
 else if argYear mod 4 = 0 then true
 else false

Open in new window