PDA

View Full Version : Date caculated field.


ABC123

John Fitzgerald Jr
01-29-2001, 07:50 AM
I need to caculate an age. I have a field, Date of Birth(DOB), Date of Death(DOD), and a third field, caculated AGE. Under field rules AGE, I entered expression, IF(ISBLANK("DOB"),0,INT((DATEDEATH)-DOB)/365.25))
I know somehow I have to also enter todays date DATE() in expression, or else incorrect age, since expression presumes everybody has already had birthday.
Thank you very much
John Fitzgerald

Melvin Davidson
01-30-2001, 05:28 AM
John,

Just a slight "tweak" in your formula and it should work:

Change
IF(ISBLANK("DOB"),0,INT((DATEDEATH)-DOB)/365.25))

TO

IF(ISBLANK("DOB"),0, INT(DATEDEATH-DOB)/365.25 )

In other words, take the INT (Integer) of the difference in dates first, then divide by 365.25. Elsewise you get the dreaded
"Operand of incorrect type"

Regards,
Melvin

Thomas Henkel
01-30-2001, 05:45 AM
John,

I tested your code and it works just fine. You are right in that children under one will be miscalculated. You may want to check to see if death date - DOB is less than 365, then somehow change to months or weeks. Weeks would be a little more accurate as you can divide by 7 to get a week, months are a little trickier since there is no set month. if you want to get an estimate, then divide by 30. for the most part, you will get a reasonably acurate count.

you might want to do something like this:

IF(ISBLANK("DOB"),0,if(INT((DATEDEATH)-DOB)

John Fitzgerald Jr
01-30-2001, 07:28 AM
Mel,thank you for the reply.
Very bizarre, still problem. Sometimes it would caculate correctly, other times incorrect. Have discovered pattern when incorrect.
Try Date of Birth 5-1-1980, and Date of Death 1-1-2001. Correct age should be 20, since birthday not attained. Caculates 21. However when you advance birthdate > 6 months from date of death caculates correctly, age 20.
I tried 8-1-1980, and got correct age. It would not caculate 7-1-1980. Is this very strange?
I tried several examples, with same results.
Thank you,
John Fitzgerald.