CleverclogsTipTime2Back in July 2003 (this is not a typo) I wrote a tip about the DATEDIF function to calculate somebody’s age based on their date of birth. With hindsight – wonderful thing – I feel this was a rather complicated way and as I take pride in offering easy methods I wanted to share, what I believe is, a much simpler way. It’s based on the fact that you can do calculations between dates.

Here’s how:

  1. Enter the person’s date of birth in, say, cell A1
  2. Select the cell in which the age is to appear (say, B1) and type the following formula:
    =INT((NOW()-A1)/365.25)
  3. Press ENTER

See absences.xlsx for an example.

I know this probably looks even worse, so let’s break it down . . .

If you would have put today’s date in a separate cell, say A1, and the date of birth in cell A2, the formula could have been as simple as =A1-A2. You then know how many days someone is alive. You then divide (hence the forward slash) that outcome by 365.25 (to cater for a leap year). And finally you will need to round the figure down, because if you are born on 12 December (someone springs to mind) you don’t turn 60 until that specific date. Oh, and the extra brackets are because of the mathematical order of operations. Parenthesis, exponents, multiply, divide, add and subtract. Please Excuse My Dear Aunt Sally, a mnemonic to help me remember the mathematical order. And isn’t it ironic that the very word, describing the technique intending to assist in remembering something, is almost impossible to remember?!

Finally, if you want to work out formulas in a workbook you inherit and don’t understand, simply click anywhere in an empty cell and type the formula. For example, when you type =INT you get a little pop-up stating “Rounds a number down to the nearest integer”. Similarly, when you type =NOW it states “Returns the current date and time formatted as a date and time”.

Related tips

  1. Calculating a person’s age – tip_22.html
  2. Countdown to your special day: anniversary, birthday, wedding, retirement or … Christmas – tip_356.php
  3. Save time entering dates – tip_442.php
  4. Insert today’s date or current time – tip_552.php
  5. Autocomplete formulas including its open bracket – tip_545.php