Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
TigerPowerTigerPower 

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??
Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell
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.

All Answers

BuellBuell
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. 
TigerPowerTigerPower
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?
BuellBuell
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
TigerPowerTigerPower
: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 characters

I 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
BuellBuell
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?
TigerPowerTigerPower
Well, yes, I tested this with a custom field on opportunity object. I haven't calculated upcoming birthdays with Salesforce standard field (birthday).

:smileytongue:
CarrieLeeCarrieLee

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 ')'

 

Here is what I have. Please HELP!!!!

 

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))
     )
BuellBuell

Give these a try instead:

 

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


CarrieLeeCarrieLee
You're awesome. Thank you, thank you, thank you. Muwah!