Review Dates

[Tom England]

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!!!

[LeCates]

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,

[Tom England]

That has worked a treat!!

Thanks

[Tom England]

I have altered the second calculation so that it is now as follows:

quote:

---

Date( Month(Status( CurrentDate)) + ReviewFreqNo - Mod( (Month(Status(CurrentDate) ) - Month(FirstReviewDate)) , ReviewFreqNo) , 1 , Year(Status(CurrentDate))))

---

Where ReviewFreqNo is a calculated field and equals the following dependant upon which review frequency a user chooses:

• Monthly = 1
  Quarterly = 3
  HalfYearly = 6
  Annually = 12

Can you try and see why this calculation isn't working

ie. FirstReviewDate = 1/12/2000

ReviewFrequency = Annually (Therefore ReviewFreqNo = 12)

BUT Next review Date = 1/12/2003 not 2002

I'm sure it used to work properly prior to the New Year. Do you think that it could have something to do with the Year Change??

Thanks