Calculate Age Years and Months 1

[BeeKpr]

Hi All,
Need help on writing a formula that will calculcate an
employees age as of the current date. Need years and months. Using CR9, Oracle database. The birthdate field is a string yyyymmdd.

thank you,

 

[synapsevampire]

Convert the string date to a real date first.

Here's the years:

http://www.kenhamady.com/form06.html

Months could be easily determined by using the datediff function of currentdate and birthdate with the birthdate year adjusted to the current year.

-k

 

[BeeKpr]

Sorry, I am a bit new to CR, can you please be more
detailed.

thank you,

 

[MJRBIM]

It's not pretty, but it works....

Use these three formulas - replacing the {TABLE.FIELD} with your birthdate field from your table.

Name: {@BD_Date}
Formula: Date ((ToNumber(Left ({TABLE.FIELD},4))),
(ToNumber (Mid ({TABLE.FIELD},5,2))),
(ToNumber (Right ({TABLE.FIELD},2 ))))

//Converts STRING to DATE

Name: {@YEARS}
Formula: IF (Date (2000,Month({@BD_Date}),Day({@BD_Date}))) > (Date (2000,Month(CurrentDate),Day(CurrentDate)))
THEN (Year(CurrentDate) - Year({@BD_Date}))-1
ELSE IF Date (2000,Month({@BD_Date}),Day({@BD_Date})) <= Date (2000,Month(CurrentDate),Day(CurrentDate))
THEN (Year(CurrentDate) - Year({@BD_Date}))
ELSE 100

//Calculates Whole Years Elapsed

Name: {@MONTHS}
Formula: IF ((Date (2000,Month({@BD_Date}),Day({@BD_Date}))) >= (Date (2000,Month(CurrentDate),Day(CurrentDate)))
AND (Date (2000,Month({@BD_Date}),1)) <> (Date (2000,Month(CurrentDate),1)))
THEN (Month(CurrentDate) - Month({@BD_Date}))+12
ELSE IF((Date (2000,Month({@BD_Date}),Day({@BD_Date}))) >= (Date (2000,Month(CurrentDate),Day(CurrentDate)))
AND (Date (2000,Month({@BD_Date}),1)) = (Date (2000,Month(CurrentDate),1)))
THEN 0
ELSE (Month({@BD_Date}) - Month(CurrentDate))

//Calculates Whole Months Elapsed

Hope it helps...

 

[BeeKpr]

Thanks MJRBIM for your formulas, they were very helpful!!!

 

[SmpTraining]

Once string converted to date use:

if date(year(currentDate), month({myDOB}), day({myDOB})) <= (currentdate) then
// Birthday today or earlier this year
year(currentDate) - year({myDOB}) -1
else
year(currentDate) - year({myDOB})

I've not tested this formula but it should be pretty close. It may cause an error if the DOB is 29th Feb on a leap year so check for that if necessary.

Steve Phillips, Crystal Consultant