function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
YuchenYuchen 

How to find out whether there is leap year between a date range?

So the requirement here is to find out whether there is leap year date (ie, 2/29/xxxx) between a date range using Formula in Salesforce.

We have two dates for our custom Object, Start Date and End Date, and the date range is usually within a year, for example:
1) If Start Date is 7/3/2003, End Date is 4/4/2004, then the Formula Output should be "YES" because there is a leap year date(2/29/2004)
2) If Start Date is 2/1/2016, End Date is 1/10/2017, then the Formula Output should be "YES" because there is a leap year date(2/29/2016)
3) If Start Date is 4/2/2004, End Date is 2/15/2005, then the Formula Output should be "NO" because there is no date for leap year for the given date range

Thanks a lot for the help!
SlashApex (Luis Luciani)SlashApex (Luis Luciani)
Hi Yuchen,

That is one interesting challenge. I think this formula will work for you.
  
/* Start Date is a leap year */
( MOD(YEAR(Start_Date__c),4) == 0 && (MOD(YEAR(Start_Date__c),100) != 0 || MOD(YEAR(Start_Date__c),400) == 0) ) ||

/* End Date is a leap year */
( MOD(YEAR(Start_Date__c),4) == 0 && (MOD(YEAR(Start_Date__c),100) != 0 || MOD(YEAR(Start_Date__c),400) == 0) ) ||

/* Diff between Start Date and End Date is 1 year and that year is a leap year */
( YEAR(End_Date__c) - YEAR(Start_Date__c) == 2 && MOD(YEAR(Start_Date__c)+1,4) == 0 && (MOD(YEAR(Start_Date__c)+1,100) != 0 || MOD(YEAR(Start_Date__c)+1,400) == 0) ) ||

/* There is at least a 7 year difference between the 2 years Ex: 2096 - 2103*/ 
(YEAR(End_Date__c) - YEAR(Start_Date__c) >= 7) ||

/* There is at least a 3 year difference between the 2 years Ex: 2094 - 2100 AND account for the year having to be divisible by 400 if div by 100*/ 
( (YEAR(End_Date__c) - YEAR(Start_Date__c) >= 3) && ( ( MOD(YEAR(Start_Date__c),100) >=4 && MOD(YEAR(Start_Date__c),100) <= 96) || MOD(YEAR(End_Date__c),100) >= 4 ) )

Let me know if it does. It should at the very least be a good start.

Good Luck!
YuchenYuchen
Hi Luis,

Thanks for the formula. Looks like it is working. I will start from here. Thank you! 
Sarah StifflerSarah Stiffler
I am trying to use this formula.  Do I put an IF function before with a yes and no as the answer if true or false?  I did the following but it seems to be backwards  I put for true, Yes and for false No, but if the year is a leap year it is showing no and yes if it is not:
IF(/* Start Date is a leap year */ 
( MOD(YEAR(Subscription_Start_Date__c),4) == 0 && (MOD(YEAR(Subscription_Start_Date__c),100) != 0 || MOD(YEAR(Subscription_Start_Date__c),400) == 0) ) || 

/* End Date is a leap year */ 
( MOD(YEAR(Subscription_Start_Date__c),4) == 0 && (MOD(YEAR(Subscription_Start_Date__c),100) != 0 || MOD(YEAR(Subscription_Start_Date__c),400) == 0) ) || 

/* Diff between Start Date and End Date is 1 year and that year is a leap year */ 
( YEAR( Subscription_End_Date__c ) - YEAR(Subscription_Start_Date__c) == 2 && MOD(YEAR(Subscription_Start_Date__c)+1,4) == 0 && (MOD(YEAR(Subscription_Start_Date__c)+1,100) != 0 || MOD(YEAR(Subscription_Start_Date__c)+1,400) == 0) ) || 

/* There is at least a 7 year difference between the 2 years Ex: 2096 - 2103*/ 
(YEAR( Subscription_End_Date__c ) - YEAR(Subscription_Start_Date__c) >= 7) || 

/* There is at least a 3 year difference between the 2 years Ex: 2094 - 2100 AND account for the year having to be divisible by 400 if div by 100*/ 
( (YEAR( Subscription_End_Date__c ) - YEAR(Subscription_Start_Date__c) >= 3) && ( ( MOD(YEAR(Subscription_Start_Date__c),100) >=4 && MOD(YEAR(Subscription_Start_Date__c),100) <= 96) || MOD(YEAR( Subscription_End_Date__c ),100) >= 4 ) ),"Yes","No")