Solved

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

Posted on 2008-02-07
Medium Priority
1,028 Views
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?
0
Question by:sfrisk
• 5
• 2

LVL 7

Expert Comment

ID: 20844019
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

Author Comment

ID: 20844425
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

LVL 7

Expert Comment

ID: 20844491
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}
``````
0

LVL 7

Expert Comment

ID: 20844938
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
``````
0

Author Comment

ID: 20896772
mnye:

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

Thanks.
0

LVL 7

Accepted Solution

mnye earned 1500 total points
ID: 20896840
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}
)
``````
0

LVL 7

Expert Comment

ID: 20896868
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
``````
0

## Featured Post

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
###### Suggested Courses
Course of the Month4 days, 3 hours left to enroll