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?