Calculate a person's age to the report

[former_member584790]

HI I have a field that contains the Patient's birthday. The report is looking for the Patients age.

I was looking for a formula that would give the years between 2 dates, with the to date being of course today's date. I guess that we don't want to round up, that is, someone is not the next year until the actual date match.

There is a command in other script languages called Subdur (subtract duration) that would give the time between two dates. I was wondering if something like this can be had in Crystal as well.

[Former Member · Accepted solution]

Hi Philky:

Try this formula:

If totext(CurrentDate,"MM/dd")>=totext({Birth Date},"MM/dd")

then Year(CurrentDate)-Year({Birth Date})

else Year(CurrentDate)-Year({Birth Date})-1

There are other formulas out there that will have you subtract the years and divide by 360 (or some other #) but if accuracy is important, this is the only way to do it as the other methods are not 100% accurate. It looks to see if the the birth month and day has passed, if it has, it subtracts the years, if it hasn't it subtracts the years and minuses 1.

Deb

[former_member584790 · Accepted solution]

Deb,

I typed this formula but got an error message

"The formua Result must be a string."

It's possible that the problem is that the field is not only containing the date but also the time. DOn't ask me why someone's birthday field contains the time. Although I have defines it as a custom field and use the date format only not time so that when it would print, it prints only the date and not the time.

is it possible however that when doing the formula, the time gets in the way somehow?

Edited by: Philky on Mar 24, 2009 2:29 PM

[Former Member · Accepted solution]

Hi Philky,

You can simply type in a formula as follows:

Year(CurrentDate)-Year({Birth Date})

Now, place this formula field in the details section of the report. You will get the age for each person.

Hope this helps you!

Regards,

Anindita

[former_member584790 · Accepted solution]

also on this form. I am getting the same error message as previous.

I tried to setup a work field that could contain the result of this expression to satisfy the error cond. that Crystal is giving me.

Every time I try to setup a Numbervar I get this error message that it's looking for a string as a result field.

I am getting quite confused already.

[Former Member · Accepted solution]

Hi

- Create a new formula field by Right click on formula field from field explorer -> new -> put a name of the formula.

- You don't need to put any variable, in the formula editor simply type the formula as Anindita mentioned:

CurrentDate - {BirthDate databse field name};

- Click on X-2 to check if any error's coming.

- Click on Save and close.

Place this formula field on the details section, and you get the age for each person.

Hope this helps!!

Regards

Sourashree

[former_member584790 · Accepted solution]

I do this, it consistently give the same error message

"Formula result must be a string".

That is why all I wanted to do was add a result string to this formula.

[Former Member · Accepted solution]

It's possible that the problem is that the field is not only containing the date but also the time. DOn't ask me why someone's birthday field contains the time.

This is because a DateTime field in a database is allways Date+Time. I believe the next SQL server will have separate fields for Date and Time, but for now we're stuck with it.

A quick google gave these results,

[http://kenhamady.com/form06.shtml|http://kenhamady.com/form06.shtml]

[http://www.forumtopics.com/busobj/viewtopic.php?t=112815&start=0&postdays=0&postorder=asc&sid=47492f323ddca688b00cb63fb45a001d|http://www.forumtopics.com/busobj/viewtopic.php?t=112815&start=0&postdays=0&postorder=asc&sid=47492f323ddca688b00cb63fb45a001d]

[http://www.crystalkeen.com/articles/crystalreports/agecalculation.htm|http://www.crystalkeen.com/articles/crystalreports/agecalculation.htm]

[Former Member · Accepted solution]

>

> You can simply type in a formula as follows:

>

> Year(CurrentDate)-Year({Birth Date})

>

> Now, place this formula field in the details section of the report. You will get the age for each person.

This will NOT give you the correct age! You MUST compare the current month/date to the dob month/date.

With the above formula, if Current Date = March 24, 2009 and DOB = June 1, 1999, if you subtract 2009-1999 you get the person is 10 years old, they are not, they are 9.

[Former Member · Accepted solution]

>

> Deb,

>

> I typed this formula but got an error message

>

> "The formua Result must be a string."

>

> It's possible that the problem is that the field is not only containing the date but also the time. DOn't ask me why someone's birthday field contains the time. Although I have defines it as a custom field and use the date format only not time so that when it would print, it prints only the date and not the time.

>

> is it possible however that when doing the formula, the time gets in the way somehow?

>

When you "typed" the formula, did you select CurrentDate and your DOB field from the field and function pickers (sometimes I've found CR acts "funky" if you type them instead of selecting them from the lists)? Time should not be an issue. I've used that formula with all sorts of backend databases and have not had an issue.

[former_member292966 · Accepted solution]

Hi Deborah,

How does your formula handle Leap Years? You're age may be off 1 day for every 4 years.

Brian

[Former Member · Accepted solution]

>

> Hi Deborah,
> 
> How does your formula handle Leap Years?  You're age may be off 1 day for every 4 years.  
> 
> Brian

That shouldn't be a problem b/c it compares the month/day first to determine which calculation to do. If the month/day is greater than or equal to today, it subtracts the years, if not it minuses one from the year. If a person is born 2/29, on non-leap years it won't count them as a yr older until 3/1 since there is no 2/29, but in a leap year on 2/29 they would be counted as a yr older.

[former_member292966 · Accepted solution]

Hi Deborah,

Ah. I see that now. That works too.

I have the occassional need to calculate age to a decimal so I have to divide by 365.25.

Thanks for clarifying.

Brian

[former_member584790 · Accepted solution]

HI I was entering the formula in the wrong place. I was entering on the field itself within the Common part of the format field, whereas when I enter it as a 'pure' formula via the Formula expert it compiles cleanly.

This formula Deborah has posted worked very well

Thank you,

Philky

[former_member584790]

Brian, Your formula gave an error message:

' A Number, or currency amount is required here'

the data type on my field is DateTime

[former_member292966]

Hi Philky,

Try this formula.

Int ((Today - {TABLE.Birthdate} / 365.25));

This will subtract the dates and calculate the years by dividing 365.25.

The Int function will prevent the age from rounding up.

Good luck,

Brian