I am writing a formula where I need a logic for calculating the earliest leap year.


(Sum(Mort1, Mort2, Mort3)*1.08^(current year-earliest LP year))+water+ECB+Taxes

What logic we can apply for earliest LP year?

  • What does your above formula corresponds to?
    – Mahmood
    Feb 24, 2017 at 6:42
  • its a currency formula. Feb 24, 2017 at 6:57

2 Answers 2


Your question made me think and this is what I have come up with. In this article, from Microsoft gave me all the necessary details to calculate Leap Year, it says

To determine whether a year is a leap year, follow these steps:

  1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.
  2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4.
  3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.
  4. The year is a leap year (it has 366 days).
  5. The year is not a leap year (it has 365 days).

To make matters simpler they have included an Excel formula as well

=IF(OR(MOD(YearValCell,400)=0,AND(MOD(YearValCell,4)=0,MOD(YearValCell,100)<>0)),"Leap Year", "NOT a Leap Year")

Now, I had to make few tweaks and additional logic implementation to make it work on Salesforce and then complete your requirements. Enough said, final version of the formula is

   MOD( Year( Today() ),400)=0,
       MOD(Year( Today() ),4)=0,
       MOD(Year( Today() ),100)<>0
,Text(Year(Today())) & " is Leap Year", 
       MOD( Year( Today() )-1,400)=0,
           MOD(Year( Today() )-1,4)=0,
           MOD(Year( Today() )-1,100)<>0
   ,Text(Year(Today())-1) & " is Leap Year", "Logic for Year (Today)-2"


What was all that if it doesn't show what you want, right? :) I added that field on Task object just for reference, you can create the field on your desired object, and the screenshot is given below

enter image description here

P.S. Before you may ask, Formula field I created for this drill is of type text, hence the casting of Text(Year(today() )).

  • thank you Mahmood..... your logic is correct. But i implemented as per my need(logic in below answer). Feb 24, 2017 at 7:42
  • Your formula calculation is fine but it is not foolproof, I afraid. For example, year 2100, which is very far from now, I know, is gonna be a leap year as per your calculation but in reality it is not. It is just an example. Secondly, you could create a separate field for year calculation and refer it in your Currency calculation formula field, for simplicity. :-)
    – Mahmood
    Feb 24, 2017 at 7:50

I have used below formula and it is working as per my requirement:

(( Mort__c + Mort2__c + Mort3__c) 
* (1.08 ^(YEAR( TODAY())+1- 
if(MOD( YEAR( TODAY() )-1, 4 ) = 0,YEAR(TODAY())-1, 
if(MOD(YEAR( TODAY() )-2,4)=0,YEAR(TODAY())-2, 
if(MOD(YEAR( TODAY() )-3,4)=0,YEAR(TODAY())-3, 
if(MOD(YEAR( TODAY() )-4,4)=0,YEAR(TODAY())-4, 
YEAR( TODAY()))))))))
+ water__c 
+ ECB__c 
+ Taxes__c

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .