Print Page | Close Window

Error checking dates that span leap years

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22773
Printed Date: 27 Apr 2024 at 5:15pm


Topic: Error checking dates that span leap years
Posted By: bremen
Subject: Error checking dates that span leap years
Date Posted: 16 May 2019 at 7:53am

Background - I have a software where I input information pertaining to training. There is a frequency, issue date, and expiration date. We have a series of crystal reports to when recurring training events are coming due for people.

The frequency field, issue and expiration dates all operate independent of each other. The software does not key off of the frequency to determine the expiration date. For this reason I wish to add some error checking to limit human error. We are audited pretty rigorously so these reports are fairly important.

Problem - Due to the frequency being useless in the database I have to create my own error checking formulas in case the guys entering data put in the wrong dates. Leap years are making this hard. 2020 is a leap year. One of our certs are has a 2 year recurrence. If a cert was issued 4-APR-2018 the cert expires 4-APR-2020. Due to the leap year there is an extra day. I can filter this out easy enough. It is starting to get tricky when I add in three year certs. If the cert spans three years than I do not have the 2020 year in field to look for. I have 2018 and 2021 as available years, so with my formulas I can not see the 2020 year.

Our certs go by the date issued and not the number of days elapsed.

I need a formula to look at the issue date and expiration and the frequency and determine if the dates match.

Example 1-

Cert A - Issue Date = 4-APR-2018 Frequency = 2 Year EXP Date = 4-APR-2020

With this data the formula should return a blank value

Example 2-

Cert A - Issue Date = 4-APR-2018 Frequency = 2 Year EXP Date = 3-APR-2020

With this data the formula should "Dates do not equal frequency"

The day and month should be equal and the year should be plus the frequency time.

How can I go about achieving this?



Replies:
Posted By: DBlank
Date Posted: 16 May 2019 at 9:11am
Maybe I am not following but Dateadd() should handle leap year just fine.
dateadd("yyyy",2,table.issuedate)

If the issuedate is Feb 29 I believe dateadd of a year would make it feb 28 of that year unless it was also a leap year.


Print Page | Close Window