Review Dates

[Tom England]

In December I posted the following:

quote:

---

We have a field called "1stReviewDate" another called "ReviewFrequency" (with defined values of annual, 6 monthly, Quarterly, Monthly) and another called NextReviewDate

Can anyone help me with the following:

For example if the 1st review date was 1/10/2001 (UK date format) and someone chose review frequency of quarterly, I want the next review date to show 1/1/2002, until that date, and then it would show the next review date of 1/4/2002.

I know how to add certain numbers of months to dates, but can't get my head round the bit where the review date stays the same until that date is reached, then rolls over to the next date. Can anyone help - PLEASE!!!

---

I received the following reply from Droid:

quote:

---

Originally posted by droid:

Hi Tom,

If you simply want to set a single next review date, the math is pretty simple. For quarterly ( in U.S. format)

code:

= Date(Month(InitialReviewDate)+3, 1, Year(InitialReviewDate))

For perpetual updates to NextReviewDate where InitialReviewDate never changes it's a bit more complex (again quarterly, U.S. Format)

code:

---

= Date(

Month(Status(CurrentDate)) + 3 - Mod( (Month(Status(CurrentDate)) - Month(InitialReviewDate)) ,3),

1,

Year(Status(CurrentDate))

)

---

Hopefully you can extrapolate the above for annual, semi-annual, and monthly frequencies. It is possible to combine all frequencies in the above formula without adding If() or Case(), but I'll leave that as an excercise for now.

Good luck,

---

The second calculation worked fine until I can back to work after New Year. For some reason it seems to be adding 2 quarters on.

I had addapted the calculation so that it would work for annual reviews, quarterly monthly etc, but these don't work either, it seems to be adding 2 of whatever review frequency is selected.

PLEASE HELP!!!

[BobWeaver]

Try this formula instead:

Date(

Round(

((Year(Status(CurrentDate)) - Year(InitialReviewDate)) * 12 +

Month(Status(CurrentDate)) - Month(InitialReviewDate) -

(Day(Status(CurrentDate)) < Day(InitialReviewDate)))

/ ReviewFrequency+.5,0) * ReviewFrequency + Month(InitialReviewDate),

Day(InitialReviewDate),

Year(InitialReviewDate))

ReviewFrequency is number of months between review dates: 1, 3, 6, 12, etc.

I just gave this formula a quick test for a number of different conditions, and it worked. But, you should give it a more thorough test.

You may need to change the < symbol to a symbol depending on when you want the next review date to change over.

Also, substitute semicolons for commas depending on which Filemaker version you have. Good luck.

[Tom England]

Reply to Bob Weaver,

Should the result be stored?

[BobWeaver]

It should be unstored. Otherwise, it won't automatically update.

The downside is that you can't index the field, so searching on the field will be slow.

[Tom England]

Thanks,

The above calculation is working fine.