# Years of Membership

## Recommended Posts

Hi, I have a database of Members that joined a Club. I have a Date Joined, now I like to track the number of years they've been a member. Is there a calculation for this or do I need to seperate the year from the date to run a calculation?

##### Share on other sites

Try this:

```Year(Date(
Month(date_joined); Day(date_joined);
Case(
Date(Month(date_joined); Day(date_joined);
Year(Get(CurrentDate))) > Get(CurrentDate);
Year(Get(CurrentDate)) - 1; Year(Get(CurrentDate))
)
)
)
- Year(date_joined)```

Calcuation result is Number.

All the stuff before the very last line of the calculation returns the year of the member's last anniversary date. You subtract the year of the date the member joined to get the total number of years they've been a member.

* Note that the result will always be a whole number, and if it's less than a year since they joined, the result is zero.

##### Share on other sites

Since Year ( Date ( x ; y ; z ) = z, you could deal directly with z, instead of going around.

##### Share on other sites

You are absolutely correct, of course! Sometimes you can't see the forest for the trees. Thanx.

I revise the formula to:

```Case(
Date(Month(date_joined); Day(date_joined);
Year(Get(CurrentDate))) > Get(CurrentDate);
Year(Get(CurrentDate)) - 1; Year(Get(CurrentDate))
)
- Year(date_joined)```

##### Share on other sites

And another way to skin that cat (same principle, different expression):

```Let([
today = Get(CurrentDate);
refdate = date_joined
];
Year(today) - Year(refdate) -
Case ( Month(refdate) > Month(today) or
Month(refdate) = Month(today) and Day (refdate) > Day (today); 1)
)```

And of course, the calculation result is unstored.

##### Share on other sites

Thanks, David. That is easier to follow.

##### Share on other sites

Thanx! It worked!!

smiley-smile

##### Share on other sites
And another way to skin that cat (same principle, different expression):

```Let([
today = Get(CurrentDate);
refdate = date_joined
];
Year(today) - Year(refdate) -
Case ( Month(refdate) > Month(today) or
Month(refdate) = Month(today) and Day (refdate) > Day (today); 1)
)```

And of course, the calculation result is unstored.

I have a very similar problem but I can't quite come to terms

with how to calculate the result.

My clients run a housing office and need to know the "length of guests stay" in years/months/days

I have a field for "move in date" and "move out date" and I can get the difference expressed as days, but that number gets awkwardly large when they have stayed for many years.

Can anyone suggest a way to get the result expressed in years, months, & days?

##### Share on other sites
Can anyone suggest a way to get the result expressed in years, months, & days?

As has been discussed in other threads, the calculation of months and days can become messy. You need to define what you consider to be a 'month' and how you are going to count 'days'. This is not as easy as it sounds.

For example, for a "move in date" of March 5 and "move out date" of July 15, what would you say is the result? 3 months and 42 days?

Another example, for a "move in date" of March 15 and "move out date" of March 2 the next year, what would you say is the result? 1 months and how many days?

So what you need to do is to establish some business rules to determine when it is a 'month' and how to count additional days beyond whole months.

##### Share on other sites

Thanks David!

You've given me some important aspects to consider before

I proceed much further.

##### Share on other sites

Year(s) and Day(s) using two dates:

Div ( Abs ( Date_A - Date_B ) ; 365 ) & " Year(s) " & Mod ( Abs ( Date_A - Date_B ) ; 365 ) & " Day(s)"

Set the calculation to text or use a custom function as a text result.

0 Year(s) 307 Day(s)

abs eliminates the need to worry about younger/older or same date.

##### Share on other sites
Div ( Abs ( Date_A - Date_B ) ; 365 ) & " Year(s) " & Mod ( Abs ( Date_A - Date_B ) ; 365 ) & " Day(s)"

This calculation returns an inaccurate result due to the assumption that a year is always 365 days.

The problem is:

"I have a Date Joined, now I like to track the number of years they've been a member."

Someone who joined 26 October 1999 will today (26 October 2007) have been a member for 8 Year(s) 2 Day(s). Clearly, for membership purposes, this should be exactly 8 years as defined by the anniversary of their joining date.

##### Share on other sites
This calculation returns an inaccurate result due to the assumption that a year is always 365 days.

The problem is:

"I have a Date Joined, now I like to track the number of years they've been a member."

Someone who joined 26 October 1999 will today (26 October 2007) have been a member for 8 Year(s) 2 Day(s). Clearly, for membership purposes, this should be exactly 8 years as defined by the anniversary of their joining date.

Div ( Abs ( Date_A - Date_B ) ; 365 )

I thought it would be obvious, even though I posted elsewhere, that you can cut the calc and only use the year part. Here it would work in a number field.

I also added an if to cover missing dates.

If ( IsEmpty ( Date_A ) or IsEmpty ( Date_B ), "" ; Div ( Abs ( Date_A - Date_B ) ; 365 ) )

You can change "" to "Missing Date", etc.

Since this calculates the years between two dates, you can adjust it to fit a particular need in a similar fashion.

##### Share on other sites
Div ( Abs ( Date_A - Date_B ) ; 365 )

I thought it would be obvious, even though I posted elsewhere, that you can cut the calc and only use the year part. Here it would work in a number field.

I don't think you get it. By ignoring the leap days, that Year calc will be off by a day for every leap year that's in the range.

If this is used in an Age calc, the age will increment early depending on how great a date range is involved. A 21 year old might be happy to know they can get into your bar five days early, but someone else might not like getting that 40th birthday singing telegram ten days before the dreaded event.

##### Share on other sites
I don't think you get it.

Reading this and the drivel in other responses where Jack posts back to himself, I am also of the opinion that he just doesn't get it. And no amount of logical reasoned argument is going to change that. In fact, comments like "your answer is wrong" are simply ignored and he goes off on a tangent with some other drivel. I just hope that people reading these posts can see through the garbage.

Yes Jack, I am on tilt. Seems it is permanent.

##### Share on other sites

Birthday or years = Div ( Abs ( Date_A - Date_B) ; 365.25 ) seems to work a bit better.

##### Share on other sites
Birthday or years = Div ( Abs ( Date_A - Date_B) ; 365.25 ) seems to work a bit better.

Yes it does work a bit better as I noted in another thread (post #40):

But at the time I also noted "But it still produces errors on a person's birthday. And we are back where we started."

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...