+ Start a Discussion
Samantha CummingsSamantha 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")
Best Answer chosen by Samantha Cummings
srlawr uksrlawr uk
Instead of using Today() for the calculation once a tenue has ended, can you use the Position_Stop_Date__c ? As that will be frozen in time..

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

srlawr uksrlawr uk
Instead of using Today() for the calculation once a tenue has ended, can you use the Position_Stop_Date__c ? As that will be frozen in time..

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 
This was selected as the best answer
Samantha CummingsSamantha Cummings
Thank you! that works! The Pseudo version was very helpful in understanding the formula! :)