Back 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 want to shared what I believe is a much simpler way. It’s based on the fact that you can do calculations between dates.
Here’s how:
- Enter the person's date of birth in, say, cell A1
- Select the cell in which the age is to appear (say, B1) and enter the following formula:
=INT((NOW()-A1)/365.25)
- Press ENTER
See http://roem.co.uk/absences.xlsx for an example.
I know this probably looks even worse, so let’s break it down…
If you had 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 is a mnemonic to help me remember the mathematical order. (And isn't it ironic that the very word, describing the technique intended to assist you 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
- Calculating a person's age
- Countdown to your special day: anniversary, birthday, wedding, retirement or ... Christmas
- Save time entering dates
- Insert today's date or current time
- Autocomplete formulas including its open bracket
Upcoming courses
Our current course schedule can be found online, but we’re obviously waiting for government guidance. In the meantime, please be aware that any of the existing courses can be taught remotely, so please get in touch if you would like to book a short 1-hour session for yourself or your colleagues.
16 April 2020
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk