Calculate the Number of Years Between Two Dates in Salesforce (Including Leap Years!)

The amount of time your company has been providing goods or services to a customer can be an important data point to track in your CRM. Long-standing customers might be entitled to various benefits or services, and this information can help to inform a sales rep how they should pursue an opportunity.

On its face, it seems like it will be super easy to pull this data, but once you scratch the surface, it’s anything but!

For example, let’s say that you have two customers: Customer 1, whose contract started on April 1, 2022, and ended on March 1, 2024, and Customer 2, whose contract lasted from April 1, 2022, through May 1, 2024. If you use the YEAR function to create a Customer Term formula field:

YEAR(End_Date__c) - YEAR(Start_Date__c)

for both Customer 1 and Customer 2, this will calculate as:

2024 – 2022 = 2

But if it’s important for your business to know that your relationship has spanned a certain amount of time, this formula will not provide you with accurate data. Say a customer is entitled to certain discounts after being a customer for longer than two years: Customer 2 qualifies for these discounts, but Customer 1 does not.

In this situation, you need to use a slightly different formula that accounts for not just the years but also the days. This formula looks like this:

(YEAR(End_Date__c) - YEAR(Start_Date__c)) + ((DAYOFYEAR(End_Date__c) - DAYOFYEAR(Start_Date__c)) / 365)

For Customer 1, this will calculate as:

(2024 - 2022) + ((61 – 91)/365) = (2) + (-30/365)

This simplifies to:

2 + (-.082) = 1.918

Thus, you’ll know that Customer 2 qualifies for the discount, whereas Customer 1 does not. Make sure your formula field contains 3 decimals if it’s important that the Customer Term not round up to a full year on the year’s the last day.

If you need to account for the 366 days in leap years, you’ll want to tweak the formula a bit more. Strangely, a year is a leap year if that year is divisible by 400 or if it’s divisible by 4 but NOT divisible by 100. For example, although 2000 was a leap year, the year 1900 and the year 2100 are not leap years. Save that factoid for your trivia night!

To track this, you’ll use the MOD function, which determines if there is a remainder.

The following returns a Boolean TRUE if the year is a leap year and FALSE if it is not:

OR( 
  MOD( YEAR( End_Date__c), 400 ) = 0, 
  AND( 
  	MOD( YEAR( End_Date__c), 4 ) = 0,
  	MOD( YEAR( End_Date__c), 100 ) != 0
  )
)

Putting it all together, your formula will be:

IF(
	OR( 
  		MOD( YEAR( End_Date__c), 400 ) = 0, 
  		AND( 
  		 	MOD( YEAR( End_Date__c), 4 ) = 0,
  			MOD( YEAR( End_Date__c), 100 ) != 0
  		)
	),
	(YEAR(End_Date__c) - YEAR(Start_Date__c)) + ((DAYOFYEAR(End_Date__c) - DAYOFYEAR(Start_Date__c)) / 366),
	(YEAR(End_Date__c) - YEAR(Start_Date__c)) + ((DAYOFYEAR(End_Date__c0) - DAYOFYEAR(Start_Date__c)) / 365)
)

Working with an Experienced Salesforce Consulting Partner

As is often the case with requirements, the devil is in the details and sometimes the right answer is a lot more involved than it seems at first brush. If you need a Salesforce partner who can help you think through the complexity to create a solution that will scale and work for you, we’d love to help. Contact us to get started.