You need to sign in to do that

Don't have an account?

Yuchen

# 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!

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)Hi Yuchen,

That is one interesting challenge. I think this formula will work for you.

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

Good Luck!

YuchenHi Luis,

Thanks for the formula. Looks like it is working. I will start from here. Thank you!

Sarah StifflerI 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")