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?

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.

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

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.