1

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

Formula

(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 '17 at 6:42
  • its a currency formula. – p.samruddhi Feb 24 '17 at 6:57
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

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

Result

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() )).

| improve this answer | |
  • thank you Mahmood..... your logic is correct. But i implemented as per my need(logic in below answer). – p.samruddhi Feb 24 '17 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 '17 at 7:50
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.