1

I have created formula field name as 'Next Birth date'.

Without code for leap years:

IF(MONTH(Birthday__c)>MONTH(TODAY()),DATE(YEAR(TODAY()),MONTH(Birthday__c),DAY(Birthday__c)),IF(MONTH(Birthday__c)<MONTH(TODAY()),DATE(YEAR(TODAY())+1,MONTH(Birthday__c),DAY(Birthday__c)),
IF(DAY(Birthday__c) >= (DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(Birthday__c),DAY(Birthday__c)),
DATE(YEAR(TODAY())+1,MONTH(Birthday__c),DAY(Birthday__c))))).

How should I modify this code to support leap years?

5 Answers 5

2

There was a Salesforce article that would have helped, but it's since gone offline. How can I make a Next Birthday formula field that respects Leap Day? The relevant content is:

IF (
    MONTH (Birthday__c)=2 && DAY (Birthday__c)=29, 
    IF (
        (DATE (YEAR(TODAY()),1,1) + 59) > TODAY(),
        (DATE (YEAR(TODAY()),1,1)) + 59,
        (DATE (YEAR(TODAY())+1,1,1)) + 59
    ),
    IF (
        DATE (YEAR(TODAY()),MONTH(Birthday__c),DAY(Birthday__c)) > TODAY(),
        DATE (YEAR(TODAY()),MONTH(Birthday__c),DAY(Birthday__c)),
        DATE (YEAR(TODAY())+1,MONTH(Birthday__c),DAY(Birthday__c))
    )
)
2
  • I like, thx! Could you just explain to me what you are doing with the +59?
    – Nielsm
    Commented Aug 31, 2016 at 12:09
  • I tried this solution. However, when I test it for moving February 29, 2016 the date field becomes blank. Commented Dec 6, 2016 at 18:58
1

You will need a formula field on that object which calculates the date of the birthday next year while keeping leap years in mind:

IF (
    MONTH (Birthday__c)=2 && DAY (Birthday__c)=29, 
    IF (
        (DATE (YEAR(TODAY()),1,1) + 59) > TODAY(),
        (DATE (YEAR(TODAY()),1,1)) + 59,
        (DATE (YEAR(TODAY())+1,1,1)) + 59
    ),
    IF (
        DATE (YEAR(TODAY()),MONTH(Birthday__c),DAY(Birthday__c)) > TODAY(),
        DATE (YEAR(TODAY()),MONTH(Birthday__c),DAY(Birthday__c)),
        DATE (YEAR(TODAY())+1,MONTH(Birthday__c),DAY(Birthday__c))
    )
)
4
  • It looks like this SQL is taken without attribution from the link in the accepted answer.
    – TRiG
    Commented Mar 3, 2015 at 11:42
  • @TRiG - can you actually get to the article? The link is broken for me!
    – Matt Lacey
    Commented Jul 23, 2015 at 5:36
  • Not now I can't, @LaceySnr. And it's not in Archive.org either.
    – TRiG
    Commented Jul 23, 2015 at 9:07
  • Hang on. It is. web.archive.org/web/20150628081204/http://help.salesforce.com/…
    – TRiG
    Commented Jul 23, 2015 at 9:08
0

The following answer comes from the Salesforce documentation. Do tell if you can't get it to work using the example: https://www.salesforce.com/us/developer/docs/usefulFormulaFields/Content/formula_examples_dates.htm#adding-dmy-to-date

If you want to add a certain number of days to a date, add that number to the date directly. For example, to add five days to a date, the formula is date + 5.

Adding years to a date is fairly simple, but you do need to check that the future date is valid. That is, adding five years to February 29 (a leap year) results in an invalid date. The following formula adds num_years to date by checking if the date is February 29 and if the future date is not in a leap year. If these conditions hold true, the formula returns March 1 in the future year. Otherwise, the formula sets the Date to the same month and day num_years in the future.

IF(


AND(
    MONTH( date ) = 2,
    DAY( date ) = 29,
    NOT( 
      OR( 
        MOD( YEAR( date ), 400 ) = 0, 
        AND( 
          MOD( YEAR( date ), 4 ) = 0,
          MOD( YEAR( date ), 100 ) != 0
        )
      )
    ) 
  ),
  DATE( YEAR( date ) + num_years, 3, 1)
  DATE( YEAR( date ) + num_years, MONTH( date ), DAY( date ) )
)
0

You can use this formula with leap handled on leap years only.

eg: If birthday passed in current leap year then will get next leap year as date.

IF(
  MONTH (BirthDay__c)=2 && DAY (BirthDay__c)=29,
  DATE( CASE(MOD(YEAR(TODAY()) - YEAR(BirthDay__c), 4),
            0,IF((DATE(YEAR(TODAY()),1,1)+59)>TODAY(),YEAR(TODAY()),YEAR(TODAY())+4),
            1,YEAR(TODAY())+3,
            2,YEAR(TODAY())+2,
            3,YEAR(TODAY())+1,2000
        ), MONTH(BirthDay__c),DAY(BirthDay__c)),
  IF(
     DATE(YEAR(TODAY()),MONTH(BirthDay__c),DAY(BirthDay__c)) > TODAY(),
     DATE(YEAR(TODAY()),MONTH(BirthDay__c),DAY(BirthDay__c)),
     DATE(YEAR(TODAY())+1,MONTH(BirthDay__c),DAY(BirthDay__c))
  )
)
0

Shortest solution I came up with, which does not ignore leap years.

ADDMONTHS(Birthdate__c, (FLOOR((TODAY() - BirthdateGP__c) / 365.2425) + 1) * 12)

Basically we takes the Birthday__c and add to that 12 * age months. Age is calculated using FLOOR.

For 2000-02-29 the next birthday will be 2022-02-28.

You must log in to answer this question.

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