Renewal Date calculation

[tryphe]

I am trying to make a renewal database. I need to put in a date that the original form was signed and then have it teh renewal field automatically calculate 4 years less 1 month from that date so if the date was 12/5/2003 the renewal date needs to be 12/4/2007 I can't seem to find a way to do this. I can do it separately by having the month and year in two different Renewal date fields as numbers but not as one field as a date.

Then I am trying to write a calculation that says if renewal date is less then or equal to today's date then Renewal "Yes" else blank. I have tried it with the separate fields but I can't get seem to get the calculation right to give me the "Yes" or blank it always reads Yes.

Nothing is working and I am not sure what I am doing wrong...Any ideas, suggestions, Help what am I doing wrong there must be a way to do this. Thanks

[ESpringer]

Well, "one month" is a very slippery concept. What's the date one month before March 30? But you'll get 30 days before the anniversary of the Signature Date (except over the century lines )with date + ((365*4)+1)-30, which is:

SigDate + 1431

Many folks recommend checking for Status(CurrentDate) rather than Today, because if you leave FileMaker running for days at a time, Today won't update; it only "notices" the date when it's launched. So, your "renewel flag" calculation would yield 1 iff:

Status(CurrentDate)>(SigDate+1431)

[DanBrill]

Did you mean 4 years less one month, or less one day? Are you using US style dates where 12/4/2003 is Dec. 4, 2003, or a date format where 12/4/2003 is April 12, 2003?

In either case, I don't think ESpringer's suggestion will work because you'll be thrown off by leap years.

Try something like

RenewalDate = Date (Month(OriginalDate)-1, Day(OriginalDate), Year(OriginalDate)+4)

to get the date 4 years less one month after the OriginalDate.

Dan

[ESpringer]

Dan,

Your point is accurate. I took the liberty of figuring that "a month" has a bit of imprecision built in anyway. But that's lazy, I admit. And I defer.

[DanBrill]

Yes, but I see your point about imprecision that I wasn't getting at first glance. 1 month before March 30 is March 1 in a leap year, or March 2 otherwise, since the answer first evaluates to Feb 30th and then adjusts accordingly. A person might want to build in a rule that says that the month of the final answer must be one less than the month of the original date, so that a month before March 30th would be Feb 28 or 29. Making such a rule work for March is the toughest part. After that the other months should be easy. But I don't have the time to puzzle that one out right now.

Cheers,

Dan

[Lee Smith]

Hi tryphe,

This will produce the date you want:

Date(Month(AnyDate)+47, Day(AnyDate), Year(AnyDate))

HTH

Lee

[tryphe]

Thanks everyone for your help I did get it to work using your suggestions. Thankfully it doesn't actually have to be exactly a month earlier...it is just a notification for us to start calling people in advance to have them renew so if it is off a day or two it isn't a biggie.But It is working perfectly now and I thank you all for your help.