Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions  
Message Icon Topic: Error checking dates that span leap years Post Reply Post New Topic
Author Message
bremen
Groupie
Groupie
Avatar

Joined: 28 May 2013
Online Status: Offline
Posts: 91
Quote bremen Replybullet Topic: Error checking dates that span leap years
    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?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8983
Quote DBlank Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.