You need to sign in to do that
Don't have an account?
Samantha Cummings
Need formula to calculate tenure field with position start and stop dates
Hello, I need help writing a formula to calculate the amount of years a contact has held a position once the Position Start Date has been populated and then for the calculation to stop once the Position Stop Date field is populated.
This is what I have so far, but i'm having trouble making the calculation stop once the position stop date field is populated
IF(ISBLANK( Position_Start_Date__c ), NULL, TEXT(FLOOR((Today()- Position_Start_Date__c)/365))& " "&"Years")
This is what I have so far, but i'm having trouble making the calculation stop once the position stop date field is populated
IF(ISBLANK( Position_Start_Date__c ), NULL, TEXT(FLOOR((Today()- Position_Start_Date__c)/365))& " "&"Years")
So I suppose you will wrap your whole formula in another IF...
Pseudo:
IF (there is no stop date)
TEXT ( Today - Start date + 'years')
ELSE (there is a stop date)
TEXT ( Stop date - start date + 'years')
Less-pseudo:
IF(ISBLANK( Position_End_Date__c),
IF(ISBLANK( Position_Start_Date__c ), NULL, TEXT(FLOOR((Today()- Position_Start_Date__c)/365))& " "&"Years"),
IF(ISBLANK( Position_Start_Date__c ), NULL, TEXT(FLOOR((Position_End_Date__c- Position_Start_Date__c)/365))& " "&"Years"))
That's probably missing a bracket somewhere :D :D
All Answers
So I suppose you will wrap your whole formula in another IF...
Pseudo:
IF (there is no stop date)
TEXT ( Today - Start date + 'years')
ELSE (there is a stop date)
TEXT ( Stop date - start date + 'years')
Less-pseudo:
IF(ISBLANK( Position_End_Date__c),
IF(ISBLANK( Position_Start_Date__c ), NULL, TEXT(FLOOR((Today()- Position_Start_Date__c)/365))& " "&"Years"),
IF(ISBLANK( Position_Start_Date__c ), NULL, TEXT(FLOOR((Position_End_Date__c- Position_Start_Date__c)/365))& " "&"Years"))
That's probably missing a bracket somewhere :D :D