Jump to content

date range in months


This topic is 8057 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 8057 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.