5 Replies Latest reply on Nov 12, 2009 12:32 PM by philmodjunk

# calculating months registered

### Title

calculating months registered

### Post

I have a database of student data.  One of the fields is the date on which the student registered for their degree programme.  I would really like is for Filemaker to do a calculation for me showing how many months have passed since that registration date and automatically update a 'months registered' field.

At the moment I have a really contrived workaround, but it would be fantastic if there was an elegant way of doing this that doesn't require manual input from me every month.

I'm on Filemaker 9 if that makes any difference.

Chris

• ###### 1. Re: calculating months registered

Let ( [ Rdate = YourRegisterDateField; today = get (currentdate) ] ;

Month(today) + (Year(today)-Year(Rdate)) * 12 - Month(Rdate) - ( Day (Rdate) < day (today) ) )

The challenge here is that a "month" isn't the same number of days each time. The above calculation assumes that if the current date is even one day of the month less than the day of the month in the registration date, the current month doesn't count as a complete month.

• ###### 2. Re: calculating months registered

You're right that it produces some funny results … I just created a new record with a registration date of 01/07/2009 and the months registered field calculates 3 months but should say 4 months.

I've taken out the last part of the equation, which I think gives me the same result as my workaround (which isn't accurate to the day but is close enough):

Let ( [ Rdate = YourRegisterDateField; today = get (currentdate) ] ;

Month(today) + (Year(today)-Year(Rdate)) * 12 - Month(Rdate) )

So that's fantastically helpful -- thanks Phil.

• ###### 3. Re: calculating months registered

"I just created a new record with a registration date of 01/07/2009 and the months registered field calculates 3 months but should say 4 months. "

That's because the calculation was designed not to count the current month until the current date's day exceeds the day of the registration month. It's a case of whether or not you want to count the current partial month as a full month or not. Taking out that clause counts the current partial month as a month.

Consider this example: (MM/DD/YY format)

Registration date: 10/31/09

Current date: 11/1/09

My original expression returns 0 months as only one day has passed.

Your modified version will return a count of 1 month.

• ###### 4. Re: calculating months registered

I'm trying to do something similar, I have a StartDate and an EndDate, and need to only know the number of months. The StartDate and EndDate will always be in monthly increments (i.e. 1/01/2009...12/01/2009, 1/15/2009...5/15/2009, etc.

TIA

jason

• ###### 5. Re: calculating months registered

then

Month(EndDate) + (Year(EndDate)-Year(StartDate)) * 12 - Month(StartDate)

Should do the trick.