Placeholder

Customer Forum

Calculated column for age

Workbooks Support Posted: 2011-01-11 15:49

We record the date of birth of people on our database. How can we report on how old they are?

Workbooks Support Posted: Wed, 19.01.2011 - 10:00

Yes you can.  You need to create a calculated column that, in effect, works out the difference between today's date and the person's date of birth and then displays this as their age in years.  This can be calculated in different ways but the formula shown below should display what you need.

Remember, where below it says cf_dob_22 you need to substitute in the name of the custom field you've created, which you can find by creating a calculated column (in a report for the type of object that uses the Date of Birth custom field) and using the dropdown picklist to select the right field.  This will put the custom field name in the formula window.

  • DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS( cf_dob_22)), '%Y')+0
Workbooks Support Posted: Wed, 14.08.2013 - 13:05

In the next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps:

  • Create a new blank report based on People. Within the Details view add a column for Person Name and Date of Birth (or whatever the name of your custom field is).

The details view now displays all Cases and their Case Type. A Summary View can be added so that we can group by Type and then Count the number of records of each type. To do this:

  • Select 'Add Summary View' and add a 'Add grouping column'. Group by 'Person Name'.
  • Add a Calculated column called 'Age', which uses the following formula:

'DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS( PARENT('Date of Birth') )), '%Y')+0'

 

This formula calculates the number of years since the date specified in your Date of birth custom field. If you aren't using a custom field called Date of birth, make sure you select the correct column when creating the formula.