Tag: Calculate Age

This technique can be used to calculate the difference between any two dates.  The worked example shows calculating age and showing this in days and in whole years.  It involves using the TODAY and TRUNCATE functions and nesting several functions together.

• Create a table for showing date of birth for required people – start this in row 3
• In cell A1, input the Today function (the today function is always =TODAY() , but can also be inserted using the Formulas tab and is in the Date & Time group)

Your Sheet will now look something like this: 

• To calculate age in days, subtract the date of birth from todays date. To enable you to copy the formula to all rows you should use an absolute cell reference to cell A1.  Your formula will read =$A$1-A4
• To convert this into years, you need to divide the age in days by 365.25, so your formula will read =C4/365.25  Were you to use formatting to turn this into whole year (i.e. format the cell to a number showing 0 decimal places, anyone over 6 months towards their next birthday would become a year older!  So we need to truncate the number, removing everything after the decimal point and only using the whole years.
• The Truncate function is =TRUNC(Number to truncate, no of decimal places to keep). In the example we want to truncate C4/365.25, but haven’t yet put the formula into a cell, as I want just the one formula, so my formula in D4 will be =TRUNC(C4/365.25,0)
• If I wanted to only display age in full years, I could not hide column C or I could have put the whole calculation in one cell, giving a formula of  =TRUNC(($A$1-A4)/365.25,0)
• Example 1 below shows the two column solution and Example 2 the one column solution