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?
sfriskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mnyeCommented:
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
0
sfriskAuthor Commented:
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?
0
mnyeCommented:
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

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

mnyeCommented:
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

0
sfriskAuthor Commented:
mnye:

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

Thanks.
0
mnyeCommented:
I would create a Custom Function out of the mod 400 logic, call it IsLeapYear or something and give it a input argument, then use it in the formula to change your parameter dates:
//StartDate formula
 
If IsLeapYear(Year({?StartDate})) then
(
 If Day({?StartDate}) = 29 AND Month({?StartDate}) = 2  Then
  Date(Year({?StartDate}, 02, 28)
 Else
  {?StartDate}
)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mnyeCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.