ShowAll Questionssorted byDate Posted
TigerPower

# Calculate anniversary dates from birthdate

Hey,
I'm trying to find out the upcoming key anniversary dates based on birthdate field. I have found samples that calculate age or next birthday of a person, but I would like to be able to calculate specific upcoming birthdays like 20th, 30th, 40th birthday...

Any ideas? I thought that this would have been quite simple thing to do, but it wasn't :smileysurprised:

It would be also interesting to know how to do this with months (for example: birthdate (yyyy-mm-dd) 1988-10-20
+ 240 months (=20 years)). How to find / calculate those upcoming dates??
Buell
Try this:

Code:
```DATE (
FLOOR(((YEAR( Start_Date__c )  * 12) + MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD) / 12)
-
IF(MOD(MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD,12) = 0, 1, 0),
IF(MOD(MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD,12) = 0, 12, MOD((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD,12)),
28
)```

Replace 'Start_Date_c' with whatever your initial date field is, and 'NUMBER OF MONTHS TO ADD' with what you want.  The second to last line '28' is whatever you want your default day of the month to be.  If you will always be calculating out in whole years (1 year, 6 years, 43 years etc.) then replace '28' with this line:

IF(AND(DAY(Start_Date__c) = 29,MONTH(Start_Date__c) = 02) , 28, DAY(Start_Date__c))

If you will be calculating out off year intervals (1.2 years, 2.7 years etc.)... well, that is a bit more work as you have to accommodate for month lengths in addition to leap years.

Hope this makes sense.  Let me know if you have any questions.

Buell
How are you planning to specify the birthdays you want to view?  Dropdowns, or will they always be the same?

To calculate a date by adding months you have to convert your starting date to months, add the number of months you want, then reconvert back to years, months and days.
TigerPower
Buell,
thanks for the advice! The birthdays will always be the same, so not any dropdowns for that purpose.

How to convert birthday ('starting date') to months?

Buell
Try this:

Code:
```DATE (
FLOOR(((YEAR( Start_Date__c )  * 12) + MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD) / 12)
-
IF(MOD(MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD,12) = 0, 1, 0),
IF(MOD(MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD,12) = 0, 12, MOD((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + NUMBER OF MONTHS TO ADD,12)),
28
)```

Replace 'Start_Date_c' with whatever your initial date field is, and 'NUMBER OF MONTHS TO ADD' with what you want.  The second to last line '28' is whatever you want your default day of the month to be.  If you will always be calculating out in whole years (1 year, 6 years, 43 years etc.) then replace '28' with this line:

IF(AND(DAY(Start_Date__c) = 29,MONTH(Start_Date__c) = 02) , 28, DAY(Start_Date__c))

If you will be calculating out off year intervals (1.2 years, 2.7 years etc.)... well, that is a bit more work as you have to accommodate for month lengths in addition to leap years.

Hope this makes sense.  Let me know if you have any questions.

This was selected as the best answer
TigerPower
:smileyhappy:
Thank You very much Buell!! Great advice!
I followed your instructions and created a new formula field (data type: date)
With first code there were no errors:

DATE (
FLOOR(((YEAR( Birthdate__c ) * 12) + MONTH ( Birthdate__c ) + 210) / 12)
-
IF(MOD(MONTH ( Birthdate__c ) + 210,12) = 0, 1, 0),
IF(MOD(MONTH ( Birthdate__c ) + 210,12) = 0, 12, MOD((YEAR( Birthdate__c ) * 12) + MONTH ( Birthdate__c ) + 210,12)),
28
)

`No syntax errors in merge fields or functions. (Compiled size: 2 945 characters)`
When replacing the second last line '28' with this code, an error occured:

IF(AND(DAY(Birthdate_c)=29, MONTH(Birthdate_c)=02), 28, DAY(Birthdate_c))

`Error: Compiled formula is too big to execute (5 308 characters). Maximum size is 5 000 charactersI used the first code and I'm now calculating all the anniversaries needed! Thanks again! This was a huge help.`

Message Edited by TigerPower on 01-08-2009 07:40 PM
Buell
That is interesting, what object is the formula for?  I tested it out on Contacts with the built in 'Birthday' field and it compiled to 4,943 characters.  Looks like you have a custom birthday field in place, any calculations going on there?
TigerPower
Well, yes, I tested this with a custom field on opportunity object. I haven't calculated upcoming birthdays with Salesforce standard field (birthday).

:smileytongue:
CarrieLee

So I'm trying to use the formula that is posted to figure out an employee's 5 and 15 year anniversary. I changed the formula for 5 years, but I keep getting the nasty Error: Syntax error. Missing ')'

DATE (
FLOOR(((YEAR( Start_Date__c )  * 12) + MONTH ( Start_Date__c ) + 60/ 12)
-
IF(MOD(MONTH ( Start_Date__c ) + 60,12) = 0, 1, 0),
IF(MOD(MONTH ( Start_Date__c ) + 60,12) = 0, 12, MOD((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + 60,12)),
IF(AND(DAY(Start_Date__c) = 29,MONTH(Start_Date__c) = 02) , 28, DAY(Start_Date__c))
)

Buell

5 Year

DATE(
YEAR(Start_Date__c) + 5,
MONTH(Start_Date__c),
IF(AND(DAY(Start_Date__c) = 29,MONTH(Start_Date__c) = 02) , 28, DAY(Start_Date__c))
)

15 Year

DATE(
YEAR(Start_Date__c) + 15,
MONTH(Start_Date__c),
IF(AND(DAY(Start_Date__c) = 29,MONTH(Start_Date__c) = 02) , 28, DAY(Start_Date__c))
)

Message Edited by Buell on 10-09-2009 10:37 AM
CarrieLee
You're awesome. Thank you, thank you, thank you. Muwah!