?
Solved

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

Posted on 2008-02-07
7
Medium Priority
?
1,028 Views
Last Modified: 2012-06-21
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
Comment
Question by:sfrisk
  • 5
  • 2
7 Comments
 
LVL 7

Expert Comment

by:mnye
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

by:sfrisk
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

by:mnye
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}

Open in new window

0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LVL 7

Expert Comment

by:mnye
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

Open in new window

0
 

Author Comment

by:sfrisk
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

by:
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}
)

Open in new window

0
 
LVL 7

Expert Comment

by:mnye
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

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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 …

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question