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

Salesforce is often only as useful as your Salesforce data calculations, workflows, and logic. For example, the amount of time your company has been providing goods or services to a customer is important data to track. Long-standing customers might be entitled to various benefits or services. 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. However, 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.

Leap Year Calculations

If you need to account for 366 days in leap years, you’ll want to tweak the formula a bit more. Strangely, a year is a leap year if it’s divisible by 400 or 4 but NOT 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)
)

Salesforce Data Calculations from an Experienced Partner

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