Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
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")
Best Answer chosen by Samantha Cummings
srlawr 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

srlawr 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 Cummings
Thank you! that works! The Pseudo version was very helpful in understanding the formula! :)