+ Start a Discussion
PProkesPProkes 

Leap Year considered in Formula

We have a "Formula (Text)" field that shows a date that is 7 years from yesterday, see below.

 

TEXT(DAY(TODAY()-1)) & "/" & TEXT(MONTH(TODAY()-1)) & "/" & TEXT(YEAR(TODAY()-1)+7)

 

However this does not consider leap years, and produces a date like 29/02/2019, even though it's not a leap year that year. Please advise how the formula can be changed to consider leap years.

Navatar_DbSupNavatar_DbSup

Hi,


Try the below formula for reference(Make changes accordingly here Receive_by_Date__c is date type field):

 

TEXT(If( mod(Year(Receive_by_Date__c)+7,4) ==0,
if(month(Receive_by_Date__c)==2,
DATE(Year(Receive_by_Date__c)+7,month(Receive_by_Date__c),day(Receive_by_Date__c) +1),
DATE(Year(Receive_by_Date__c)+7,month(Receive_by_Date__c),day(Receive_by_Date__c))),
DATE(Year(Receive_by_Date__c)+7,month(Receive_by_Date__c),day(Receive_by_Date__c))))