How to: Calculate your age in days and whole years in Excel

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
    Example 1
    Example 2

    On 17th October 2011, these are the answers

 

 

 

Excel Tips: Calculate the weekday from a date

You can display the weekday for dates stored in Excel.  This is especially useful for checking if due dates fall during the working week.  To do this you need to use the weekday function and custom formatting features of Excel.  The screenshot below are from Excel 2010, but this work in earlier versions too.

  • Ensure the date on your spreadsheet is formatted as a date
  • Insert the weekday function
  • The weekday function has two arguments – the date (or cell where the date is and a return type.  The return type is optional and for this to work correctly should be left blank. 
  • Format the cell using custom formatting – to do this you will need to open the format cells dialog box, Ctrl+1 is a keyboard shortcut to do this which works in all Excel versions.

On the Number tab, choose Custom and in the Type box enter ddd  to display the week day in its three letter version or dddd to display the week day in full.