You need to sign in to do that
Don't have an account?
![TigerPower TigerPower](https://dfc-org-production.my.site.com/img/userprofile/default_profile_45_v2.png)
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??
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??
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
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.
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?
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.
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
:smileytongue:
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))
)
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))
)