function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ltremaineltremaine 

Calculate a date exactly 2 years in the future from the Loss Date (accounting for Leap years)

I have a custom Date field called 'Loss Date' (Loss_Date__c). 
I have another Custom formula (date) field called 'SOL Date' (SOL_date__c)
The 'SOL Date' needs to equal a date exactly 2 years in the future from the Loss Date (accounting for Leap years)

The formula I have is below.

Any Ideas where its wrong?

 

 

DATE(Year(Loss_Date__c)+ floor((MONTH(Loss_Date__c) + 24 -1) / 12), mod(MONTH(Loss_Date__c) + 24 / 12) + 1, day(Loss_Date__c)) - 1

Alex_ConfigeroAlex_Configero

 

Hmm interesting challenge. Let's see, leap years are divisible by 4, so something like

 

IF(

  OR(

    mod(YEAR(Loss_Date__c),4)=0,

    mod(YEAR(Loss_Date__c)+1,4)=0,

  ),

  Loss_Date__c+731,

  Loss_Date__c+730

)

 

Which basically says, if this year or next is leap, add 2*365 days + 1 for the leap year, otherwise just add 2*365. I did this quickly so I hope I'm not overlooking anything.

sfdcfoxsfdcfox

Leap years are a bit more complex, because of the 100-year rule. Thus, the formula is a bit uglier, but still manageable:

 

 

(IF(MOD(YEAR(Loss_Date__c)  ,400)=0,1,
IF(MOD(YEAR(Loss_Date__c)  ,100)=0,0,
IF(MOD(YEAR(Loss_Date__c)  ,  4)=0,1,0)))+
IF(MOD(YEAR(Loss_Date__c)+1,400)=0,1,
IF(MOD(YEAR(Loss_Date__c)+1,100)=0,0,
IF(MOD(YEAR(Loss_Date__c)+1,  4)=0,1,0))))+
(Loss_Date__c+730)

If the year is divisible by 400, it's a leap year, but if not, and it's divisible by 100, it's not a leap year, but if not, and it's divisible by 4, it is a leap year. I use a 0 or 1 mechanism in my formula so that the results will always evaluate as one of three branches:

 

(0+0)+(Loss_Date__c+730)

 

(0+1)+(Loss_Date__c+730)

(1+0)+(Loss_Date__c+730)

mark henrimark henri
Hello sir,
well,  t think soo you should hire an accountant or accountant advisor maybe they will help you or solve your problem and one of my friends is also facing the same problem. and if want to know more about accounting services (https://planetaryaccountingandmarketing.com/accounting-services/) then you can visit here