function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
FKTomFKTom 

I Have a Formula that isn't calculating....

I am running a Database of Club members, and want to be able to track how long someone has been a member. I have a custom field "Initial_Membership_Date" I have a second custom field "Years_of_Membership" "Years_of_Membership" is a calculated Number field with the following formula: YEAR(TODAY() )- YEAR(Initial_Membership_Date__c) Even in cases where the "Initial_Membership_Date" is properly populated with a date, the field remains blank. Anyone have any thoughts?
Best Answer chosen by Admin (Salesforce Developers) 
Kent ManningKent Manning

Hi FKTom,

 

Glad you figured it out!  I kind of wondered if you were putting the formula's in the wrong place.

 

As for your question about attained age, I would think you could use something like your initial formula where you would take the year of the birthday field and subtract it from year of today since it doesn't matter what day of the year you change to 40.

 

Year(Today()) - Year(Birthdate).

 

Maybe that's to simple... I guess you could try it though.

All Answers

Kent ManningKent Manning

What is the field type of the Initial_Membership_Date__c field? What is the field type of the Years_of_Membership__c field?

 

When you enter the fromula in the editor do you get any syntax errors?

 

I suspect that you are dealing with "Date" field type vs a "Datetime" field type and one of the items needs to be changed to make the calculation work correctly. 

FKTomFKTom

The Initial_Membership_Date field is a Date field  and the other is a Number field, given that all I am extracting is a day, would a datetime field calculate differently?  As for the Syntax, the Formula editor gives me the green light, which is why I am perplexed....Do I need to do something where I convert the entry to a date number or something like that?  I am new to SF, so I am not sure what is wrong, the Calculated Field is just blank, it doesn't give me any error messages, so I can't figure out if the problem is that I am utilizing the formula wrong, or whether it won't work on imported data, or something else....thanks for any help you can offer!

 

PS...I assume in SF that the (Current field=) is taken for granted?  As in Current field= Field A + Field B would just be FieldA + FieldB in the Syntax? 
JakesterJakester

Hi FKTom,

 

I think this is more complicated that it first appears. You would want to start by clearly defining various cases and how you'd want them to display. For example, it's been 1 day since they started their membership. Does that show anything in the Years A Member field? Does it show a zero? A one? Now, it's been 1 month. Does that change? 6 months? 364 days? 365 days? 366 days? How about 730 days? What about leap years? Once you've got it fully defined, you're going to have to write it all into the formula.

FKTomFKTom
I think I envision this as starting with Year 1 immediately (as in current year qualifies as a year even if not complete) and then each successive full year would register as a year, but incomplete years would not move the clock.  I envision this as being based upon an anniversary date...once that date is crossed, the clock ticks 1 year, but membership up to and inclusive of that date has no impact.   Any thoughts on the formula to do that?  If I could even get a formula to calculate and begin to tweak it as I go, that would at least get me moving in the right direction...
JakesterJakester
I don't have an example formula, but I can tell you you're going to probably end up using the following functions: floor(), year(), date(), and, of course, several if() fuctions. Good luck!
Kent ManningKent Manning

Hi FKTom,

 

I agree with Jakester.  Your formula is going to be much more complex than just subtracting the year of membership from the year of today, and it is going to require some IF statements or CASE statements to calculate correctly. 

 

However, I did create an object in my sandbox that has the same fields that you created.  Initial Membership Date (as a date field), and a second field that was a formula field called Number of Years a Member.  The syntax of the formula was:

 

Year(Today()) - Year(Initial_Membership_Date__c)

 

The formula worked as expected.  However, two notiable things did happen:

 

1)  when the Initial Membership Date is the same year as today, then zero showed up in my "Number of years a member" field. 

 

2) When the Initial Membership Date was in the future (i.e. 2010), the "Number of years a member" field was negative (as one would expect).

 

I'm not sure why your formula didn't work.  One option to troubleshoot the problem would be to create a couple of temporary formula fields on your page layout that calculate the year of Today, and the Year of "Initial Membership Date"

 

TempFieldA = Year(Today())

TempFieldB = Year(Initial_Membership_Date__c)

 

See if these give you correct results and then proceed from there.

 

 
FKTomFKTom

Thanks to both you and Jakester for your thoughts on the formula.  I eventually intend this eventually to be:

 

IF (YEAR(TODAY())-YEAR(INITIAL_MEMBERSHIP_DATE__C)>0, YEAR(TODAY()-YEAR(INITIAL_MEMBERSHIP_DATE__C, (YEAR(TODAY())-YEAR(INITIAL_MEMBERSHIP_DATE__C)+1)

 

I was doing a test drive with a simple formula first (in this case the clause of the if, then formula.  This seems to be a broad problem, because none of the formulas in my fields are calculating....

 

I have an Age field (Number field) which should calculate as follows:

 

FLOOR((TODAY()-Birthdate)/365.2425) 

 

Where Birthdate is a field in the template, and not a custom field...NO LOVE.

 

Oh, wait, I might be a dummy...I was just getting ready to do the troubleshooting step and saw the field types...Should these be FORMULA fields?   

 
FKTomFKTom

Okay...I am a doofus.  That was the issue.  I assumed the field type to be based on output, and didn't see the "formula" field type.

 

Thanks for both of your help.  I will now begin to tinker with that formula to get the right counter, I can see that the limitation of my initial thought is that it does not have the anniversary mechanism based upon the date someone joined...

 

I think I will use this:

 

IF (YEAR(TODAY())-YEAR(Initial_Membership_Date__c)>0, (FLOOR((TODAY()-Initial_Membership_Date__c )/365.2425 )), (YEAR(TODAY())-YEAR(Initial_Membership_Date__c)+1))

 

Any thoughts?

 

On the same subject...any thoughts on a formula that would represent your attained age in a year? 

 

For example, if I turn 40 at any point this year, even if it hasn't happened yet, it would record me as 40?

 

In triathlon they "age us up" so that we race at the age we will attain in any given year.  I want to calculate that for our membership, because we will use this data for race registrations.

 

 

Message Edited by FKTom on 04-14-2009 10:54 AM
Kent ManningKent Manning

Hi FKTom,

 

Glad you figured it out!  I kind of wondered if you were putting the formula's in the wrong place.

 

As for your question about attained age, I would think you could use something like your initial formula where you would take the year of the birthday field and subtract it from year of today since it doesn't matter what day of the year you change to 40.

 

Year(Today()) - Year(Birthdate).

 

Maybe that's to simple... I guess you could try it though.

This was selected as the best answer
FKTomFKTom

Thanks for all of your help.  I have been trying to look at that very formula, but for some reason have not been confident that it would yield the right result in all cases, unlike the # of years as a member, this field has real implications....

 

Thanks again. You and Jakester both got me going in the right direction.