Mar

26

Calculating Ages

Filed Under Bento Tips 

It is fairly simple to add a field to a Library which will show you someone’s age. You need to add a Calculation field to your Library and set the formula to be:

Addendum
If you are using Bento 3 or 4 (and presumably other future versions) you can use an undocumented math function to simplify the calculation. If you edit the formula to read ‘Floor( ([Today] – [Birthday]) / (60 * 60 * 24 * 365.25) )’ then Bento will round the age down. Essentially you can remove the ‘- 0.5′ element. Note that there must not be a space between the word Floor and the opening bracket.

Calculating Ages


The results can be seen below and please note that the image was created on 26 March 2008 which should put the ages into context.

Calculating Ages

To explain what is happening, here is a breakdown of the formula.

[Today] – [Birthday] simply subtracts the birthday from today’s date. The value returned is in seconds which then has to be converted into years.

This conversion is done by dividing the number of seconds by the number of seconds in a year and the simplest way of working that out is to multiply 60 seconds by 60 minutes by 24 hours by 365.25 days.

Finally, Bento rounds numbers and this has to be taken account of by subtracting 0.5 from the result. This can be more clearly understood if the formula is changed to remove the subtraction of 0.5 and the number of decimal places to show is increased from none to 6:

Calculating Ages

Here you can see that the first two people are correctly calculated as being 28 whilst the third person is 27 since their birthday is not until tomorrow. However, if the number of decimal places is reduced to zero again Bento rounds the number and 27.997424 becomes 28. This is because Bento’s rounding works by rounding down if the decimal places are below 0.5 and rounding up if they are equal to or above 0.5.

Therefore subtracting 0.5 from the calculation ‘shifts’ everything into the right place for rounding. Here’s what the ages look like with the proper formula applied and the results shown to 6 decimal places:

Calculating Ages

Although the first two records are now showing the age as 27 the decimal portion is over the magic 0.5 threshold so they get rounded to 28 and the last record is below the threshold so stays at 27.

A Problem
There is a problem with all of this however because Bento does not, as of version 1.0v2, automatically update the value of Today once a record has been created. This means that people’s ages will never change. However the calculation can be forced to update itself by simply entering the editing dialogue for the field itself and then closing the dialogue. The editing dialogue can be displayed either by double-clicking on the field in the Fields list or by choosing ‘Edit’ from the ‘Additional Commands’ button below the Fields list.

Comments

18 Responses to “Calculating Ages”

  1. Nuwan on May 27th, 2008 5:45 am

    Very Useful coz I’m doing a Talents data base for my company.. Thanks a LOT

  2. Max on July 6th, 2008 11:01 am

    Is there a way to create a form view of all birthdays in the next, say, 7 days? Can’t get my head around it…

  3. Simon on July 7th, 2008 8:52 am

    Hi MAx. You can find the answer in this post in the Bento forums.

  4. Kay on July 11th, 2008 1:39 am

    But there is a problem for years of birth prior to 1941 – get negative number (136 below the actual age). Would love solution.

  5. Simon on July 11th, 2008 9:24 am

    Kay, it seems to be fine for me and a birthday of 25 March 1938 correctly reports the person as being 70. I wonder, is your system set so that the dates display with two digits for the year rather than four? If so then I would guess that Bento is doing what most applications will do and is using a cut-off to decide whether dates are in the 1900 range or the 2000 range and for Bento it is 41.

  6. Ain Kuru on July 25th, 2008 10:03 pm

    Folks

    The formula works up to the minus sign and then shows an error. Any ideas?

  7. Simon on July 25th, 2008 10:15 pm

    Try clearing out the Calculation area and starting again. I’ve had cases in the past where Bento doesn’t properly pick-up the use of Today or the Birthday field which breaks the formula. You can also simplify it slightly by making the formula:

    (([Today] – [Birthday]) / 31557600) – 0.5

    Finally, make sure you have spaces between, well, everything since Bento is is a bit fussy about that.

  8. Edwin Jodan on May 9th, 2009 11:43 pm

    can someone help me to calculate the age but when under one year to show months and when under 1 month, to show weeks. Thanks

  9. Simon on May 10th, 2009 1:12 pm

    Edwin, since Bento’s calculated fields do not allow conditional statements it’s not possible to construct a calculation that relies on something like: if age is less than one month then show weeks, else if age is less than one year show months else show years. You could possibly have three fields, one showing years, one months and one weeks but you would end up having to show all three.

  10. Catarina Varão on August 6th, 2009 3:03 pm

    Unfortunately it didn’t work for me… it always show the yellow alert symbol no matter what type of “results” I choose (number, duration, text, date…)

    Any tips?

    Thank you ;)
    CV

  11. Florian on August 6th, 2009 3:43 pm

    Hi Cat,

    please tell us exactly what you typed into your calculation.

  12. lele on December 14th, 2009 7:12 am

    For those who had a negative number: I had too and then I remembered that my OS use a comma (,) instead of the dot (.) for the decimals. So, if you copy and paste the formula above, remember to change 365.25 to 365,25.

  13. Florian on January 7th, 2011 5:09 pm

    Tip: if you simply leave out the last parameter in the main formula (making it 60 * 60 * 24), you get the number of days (nights) between two dates. That is useful for managing a vacation-rental place, for example.

  14. Aaron Smith on June 8th, 2011 2:07 pm

    THANKYOUTHANKYOUTHANKYOUTHANKYOU!

  15. Paige on June 21st, 2011 5:57 am

    This works great, but for empty birthday fields the age shows “-1″ due to rounding. Any workarounds for this?

  16. Simon on June 21st, 2011 8:28 am

    There is a new trick you can use in Bento 3 and Bento 4 which include some undocumented math functions. I’ll update the main article but if you use this formula you’ll get zeros instead:
    Floor(([Today] – [Date Field]) / (60 * 60 * 24 * 365.25))

    Essentially the ‘-0.5′ is omitted because the Floor() function rounds the number down.

    Note that there cannot be a space between the word Floor and the opening bracket.

  17. Kellie on August 6th, 2011 11:06 pm

    This there a way to make the age appear in years and months?

  18. Simon on August 9th, 2011 1:44 pm

    There possibly is but off the top of my head I can’t think how you would do it based on the limited date calculations that are possible in Bento. Someone else might have an idea how to do it however.

Leave a Reply