date range in months

[Daniel Shanahan]

i'm trying to find the total number of months in a payment database. i have created a "Payment Begin" field (Date) and a "Payment End" field (Date). Also have a "Months Remaining" field (Calculation) which is PE - PB. However, the answer is in years and i'd like to convert it to months. I tried to multiply it by 12 but that didn't work. i should also mention that there could be 5 to 10 years between the PB and the PE. so what i really want is to know based on the PB date and the PE date AND the current date (Today), how many months are left on the payment. any ideas?

[Vaughan]

How would you work it out on paper? Do it now. Write doen the steps that you did. Use that as a starting point for the algorithm.

I did it a moment ago and the first question I had was "if the start date is 12 March 1999 will the first month be this month of the first whole month?" This is a question of policy that only you can answer. Similarly the question needs to be asked for the last month.

I came up with the algorithm...

((Year(enddate) - Year(startdate)) * 24) + Month(enddate) - Month (startdate)

Check that it works for you.

[Daniel Shanahan]

Thanks for the script, vaughan. it helped remove some mental blocks for me... and gave me some insight into Date functions. i ended up with the following script:

Case(Pledge Begins > Today, ((Year(Pledge Ends) - Year(Pledge Begins)) * 12) + Month(Pledge Ends) - Month(Pledge Begins), Case(Today* Pledge Ends, "0",((Year(Pledge Ends) - Year(Today)) * 12) +Month(Pledge Ends) - Month(Today)))

i don't think i would have come up with this had you not responded to my query.

thanks!

-------------------------

Dan

This looks good, but I wouldn't use the Today function. Today is only

calculated when the FileMaker Pro program is first launched. This

causes

a problem with databases hosted on a server (for instance) because if

the program isn't restarted once a day the Today calculation will be

wrong.

Instead use the Status[CurrentDate] or Status[CurrentTime] functions as

is appropriate, and make sure the calculation is UNSTORED to force FMP

to re-evaluate it each time it is needed.

Vaughan

[Vaughan]

Use the Status[CurrentDate] and Status[CurrentTime] functions instead of the Today function. FileMaker Pro only calculates Today whenn the database is opened: if you leave it open for a week Today will be incorrect!

Status[CurrentDate] will recalculate each time the function is needed if the calculation is set to unstored.