Jump to content
Doris

Years of Membership

Recommended Posts

Doris

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?

 

Thanks for your help!

Share this post


Link to post
Share on other sites
cando

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 this post


Link to post
Share on other sites
comment

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

Share this post


Link to post
Share on other sites
cando

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 this post


Link to post
Share on other sites
David Head

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 this post


Link to post
Share on other sites
cando

Thanks, David. That is easier to follow.

Share this post


Link to post
Share on other sites
ballymuck
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 this post


Link to post
Share on other sites
David Head
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 this post


Link to post
Share on other sites
ballymuck

Thanks David!

 

You've given me some important aspects to consider before

I proceed much further.

Share this post


Link to post
Share on other sites
Jack Rodgers

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 this post


Link to post
Share on other sites
David Head
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 this post


Link to post
Share on other sites
Jack Rodgers
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 this post


Link to post
Share on other sites
Ender
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 this post


Link to post
Share on other sites
David Head
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 this post


Link to post
Share on other sites
Jack Rodgers

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

Share this post


Link to post
Share on other sites
David Head
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):

 

http://filemakertoday.com/com/showthread.php?t=15129&page=4

 

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

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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




×
×
  • Create New...

Important Information

Terms of Use