Future Dates

[Tom England]

code:

---

Sorry that this post looks like it is 'Code' but it is the only way it will be accepted!!??

I while ago I posted a query along the following lines. Users enter first review date, then users enter the frequency of the review, then a calculation shows the next review date until that date is reached, then rolls over to the next one.

We have the following fields:

1) FirstReview = Date

2) ReviewFrequency = either monthly(=1) Quarterly (=3) HalfYearly (=6) or Annual (=12)

3) NextReviewDate (calculation based on above)

I am using the following calculation suggested by someone when I posted this before:

Date(Round(((Year(Status(CurrentDate)) - Year(FirstReview)) * 12 +

Month(Status(CurrentDate)) - Month(FirstReview) -

(Day(Status(CurrentDate)) < Day(FirstReview)))

/ ReviewFrequency +.5,0) * ReviewFrequency + Month(First Date),

Day(FirstReview),

Year(FirstReview))

It works excellently UNLESS REVIEW FREQUENCY IS MONTHLY (ie. it just adds one month on to FirstReview) Then the following happens.

If I put 1/4/2002 in for FirstReview, Monthly as ReviewFrequency (=1) NextReview shows 1/5/2002. EXCELLENT. But if I put 10/4/2002 for FirstReview(in future) it shows 10/3/2002!!

Can anyone help?? Please!!

[IdealData]

Tom, consider a slight change here - use whole weeks !

At least the reviews won't occur on a SAT/SUN, and the maths is much simpler

4 weeks = 1 month

13 weeks = 1 qtr

etc...

[Tom England]

But it may always have to be 1st of a month, or 10th of month or whatever, so can't really use that.

Thanks anyway!

[ April 03, 2002, 06:17 AM: Message edited by: Tom England ]

[djgogi]

quote:

---

Originally posted by Tom England:

code:

---

Sorry that this post looks like it is 'Code' but it is the only way it will be accepted!!??

I while ago I posted a query along the following lines. Users enter first review date, then users enter the frequency of the review, then a calculation shows the next review date until that date is reached, then rolls over to the next one.

We have the following fields:

1) FirstReview = Date

2) ReviewFrequency = either monthly(=1) Quarterly (=3) HalfYearly (=6) or Annual (=12)

3) NextReviewDate (calculation based on above)

I am using the following calculation suggested by someone when I posted this before:

Date(Round(((Year(Status(CurrentDate)) - Year(FirstReview)) * 12 +

Month(Status(CurrentDate)) - Month(FirstReview) -

(Day(Status(CurrentDate)) < Day(FirstReview)))

/ ReviewFrequency +.5,0) * ReviewFrequency + Month(First Date),

Day(FirstReview),

Year(FirstReview))

It works excellently UNLESS REVIEW FREQUENCY IS MONTHLY (ie. it just adds one month on to FirstReview) Then the following happens.

If I put 1/4/2002 in for FirstReview, Monthly as ReviewFrequency (=1) NextReview shows 1/5/2002. EXCELLENT. But if I put 10/4/2002 for FirstReview(in future) it shows 10/3/2002!!

Can anyone help?? Please!!

---

Well you don't need to make it so complicate.

Try this:

code:

---

NextReview=Date(Day(FirstReview),Month(FirstReview)+ReviewFrequency,Year(FirstReview))

or

NextReview=Date(Month(FirstReview)+ReviewFrequency,Day(FirstReview),Year(FirstReview))

Whatever is your date format

You don't need to worry about year change.

FM know how to deal with it.

HTH

Dj

[Fitch]

Your example didn't quite make sense to me (typo?) and your formula gave me a headache. Here's what I came up with:

Date (

Case (

(Month (FirstReview) + ReviewFrequency) > 12,

(Month (FirstReview) + ReviewFrequency) - 12,

Month (FirstReview) + ReviewFrequency),

Day (FirstReview),

Case (

(Month (FirstReview) + ReviewFrequency) > 12,

Year (FirstReview) + 1, Year (FirstReview)

))

[djgogi]

quote:

---

Originally posted by Fitch:

Your example didn't quite make sense to me (typo?) and your formula gave me a headache. Here's what I came up with:

Date (

Case (

(Month (FirstReview) + ReviewFrequency) > 12,

(Month (FirstReview) + ReviewFrequency) - 12,

Month (FirstReview) + ReviewFrequency),

Day (FirstReview),

Case (

(Month (FirstReview) + ReviewFrequency) > 12,

Year (FirstReview) + 1, Year (FirstReview)

))

---

I insist for US data format (mm,dd,yyyy) use

code:

---

NextReview=Date(Month(FirstReview)+ReviewFrequency,Day(FirstReview),Year(FirstReview))

where result calculation is date and Reviewfrequency is an number

Dj

[Fitch]

That's what happens when I go have coffee and don't refresh the page -- someone beats me to the post. Even worse... Dj's solution is better than mine! It never occurred to me that FileMaker would evaluate a Month(13) to January of the following year! That's very cool! So even though you can't actually type a date like that in a date field, you can do it in a calc. That is good to know.

[Tom England]

Thanks for your input but unfortunately the responses haven't quite given the results required.

DJ - Your suggestion just adds a certain number of months onto the FirstReview date. I need the NextReview date to roll over once this date is reached. Also if the first review date is 30th April 2002, and review frequency is monthly (ie. +1) the NextReview date should show 30th April as it has not occurred yet, your suggestion shows 30th May. (Have I explained that correctly?)

Fitch - Your suggestion also almost works! but it doesn't 'Roll Over' once the next review date has been reached.

Someone must have used a similar thing in the past?? Your responses are greatly appreciated. Thanks

[djgogi]

quote:

---

Originally posted by Tom England:

Thanks for your input but unfortunately the responses haven't quite given the results required.

DJ - Your suggestion just adds a certain number of months onto the FirstReview date. I need the NextReview date to roll over once this date is reached. Also if the first review date is 30th April 2002, and review frequency is monthly (ie. +1) the NextReview date should show 30th April as it has not occurred yet, your suggestion shows 30th May. (Have I explained that correctly?)

Fitch - Your suggestion also almost works! but it doesn't 'Roll Over' once the next review date has been reached.

Someone must have used a similar thing in the past?? Your responses are greatly appreciated. Thanks

---

Well, I have to admit it wasn't so clear what you've realy wanted. But now...

code:

---

NextReview=

If(Status(CurrentDate) < FirstReview,FirstReview,

If(

Mod(Month(Status(CurrentDate))-Month(FirstReview)+

12*(Year(Status(CurrentDate))-

Year(FirstReview)),

ReviewFrequency)=0,

Date(Month(Status(CurrentDate))+

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

ReviewFrequency*(Day(Status(CurrentDate))>= Day(FirstReview)),

Day(FirstReview), Year(FirstReview))

,

Date(Month(Status(CurrentDate))+

12*(Year(Status(CurrentDate))-

Year(FirstReview))-

Mod(

Month(Status(CurrentDate))-Month(FirstReview)+

12*(Year(Status(CurrentDate))-Year(FirstReview)),

ReviewFrequency)+

ReviewFrequency,

Day(FirstReview),

Year(FirstReview))

)

)

However remember that the way you have formulated the problem, ie same day after frequency*month has precluded the use of days 29,30 and 31 since they don't appear in every month of year.

If it's not a case then there are two possibilly scenario to deal with:

1. Take first available day from next month, ie

if your startDate was 08/31 and freq for ex. 1 than take 10/01

2. Take the last day of currentreview month, ie if your startDate was 08/31 and freq for ex. 1 than take 09/30

The first scenario is already included in formula I gave you, and it will work fine for every month exept when an non existing day appear in february; in that case the nextreview will fall either on 03/01 or 03/02 or 03/03.

To implement the second scenario you'll need another field, NextReviewCorrected calculated in this way

code:

---

If(Day(NextReview) != Day(FirstReview), Date(Month(NextReview), 0, Year(NextReview)), NextReview)

Hey Finch this one is my favorite date formula

Date(Month(NextReview), Day(NextReview)-Day(NextReview), Year(NextReview)) (of course 0 is Ok at place of Day(NextReview)-Day(NextReview) )

for it's insane beauty

HTH

Dj

[ April 05, 2002, 12:02 AM: Message edited by: dj ]

[Tom England]

DJ,

Thus far you have almost warranted an award for saving my sanity! Just one lat thing, should the result be stored / indexed??

[ April 05, 2002, 02:17 AM: Message edited by: Tom England ]

[Fitch]

Date(Month(Date), 0, Year(Date)

OK, dj, you're scaring me now.

Keep up the good work.

"I put instant coffee in my microwave and went back in time." S. Wright

BTW, it's FITCH -- so far today I've been Finch and Flitch.

[Tom England]

OK, got that Mitch

[djgogi]

quote:

---

Originally posted by Tom England:

DJ,

Thus far you have almost warranted an award for saving my sanity! Just one lat thing, should the result be stored / indexed??

---

Since I've used globals for FirstReview and Frequency my calcs were unstored but if you need them indexed just turn indexing on from storage setings of define fields

Dj

[djgogi]

quote:

---

Originally posted by dj:

quote:

---

Originally posted by Tom England:

DJ,

Thus far you have almost warranted an award for saving my sanity! Just one lat thing, should the result be stored / indexed??

---

Since I've used globals for FirstReview and Frequency my calcs were unstored but if you need them indexed just turn indexing on from storage setings of define fields

Dj

---

Quoting myself.

Since we have used Status(CurrentDate) in the calculation, to make it work properly as stored calc some of referenced fields must change to trigger the update.

So if you need them indexed you should have to force them to update. For ex setting the frequency to itself (yes without changing it's actual value)

Or more simply replace in formula Status(CurrentDate) with Today

Dj

[ April 09, 2002, 02:52 AM: Message edited by: dj ]