Calculating the age of a person or object in Excel is a very useful feature. This can be applied to any need to calculate the time elapsed between 2 dates. There are several ways of doing this depending on your needs. In this video I demonstrate the most useful formulas for calculating age in Excel. All you need is the start date or date of birth (DOB) (which is fixed) and the end date which will be the current date in a dynamic form so that age is recalculated as the current date advances. To do this use the function “Today()” which displays the current date obtained from the computer’s internal clock.
- To calculate age in whole years between date of birth or start date and the current date using the “datedif” function. Here is the formula: =DATEDIF(start date,TODAY(),”y”). The current date in this formula is dynamic (Today()) that is it updates automatically each day from the computer’s internal clock.
- Calculate age from date of birth and current date located in a dedicated cell.
Will need to lock source cell with F4 so that can populate formula down a column.
Here is the formula: =DATEDIF(DOB,$C$3,”y”), note that the source cell for the current date has $C$3 denoting that it is locked with F4. The date in C3 should also be dynamic which is achieved with the formula: Today().
- Calculate age with fractions of a year with this formula: =YEARFRAC(DOB,TODAY()), where DOB is the cell location with the date of birth or start date. Note cells must be formatted with number and say 2 decimal places. To get years an months the decimal will need to be multiplied by 12 eg 62.14 would be : 0.14 x 12 = 1.68 months or 2 months rounded up.
- Calculate age in years, months and days with this formula: =DATEDIF(C6,TODAY(),”Y”)& ” years “&DATEDIF(C6,TODAY(),”Ym”)& ” months “&DATEDIF(C6,TODAY(),”md”)& ” days “
To make it clear which numbers represent “years”, ”months”, or “days” the formula prints text denoting these periods. This can be cumbersome to contain in a column so one might head the column with “age yrs/mths/days” then replace the years, months, and days in the formula with “/”. See Age #5 on Excel spreadsheet.
- This is a bit long winded resulting in a very wide column. To get around this could just leave out days and just have “years and “months” or better still head the column with “Age Years/Mths “ then use the following formula: =DATEDIF(C6,TODAY(),”Y”)& ” / “&DATEDIF(C6,TODAY(),”Ym”)
Here is a video tutorial on how to calculate age:
Here are some time stamps for browsing the content of this video:
00:00 Beginning
00:33 Calculate age in years with Excel with =datedif function
00:58 Function to display current date in Excel =Today()
01:40 Excel datedif function using a fixed cell reference for current date
03:09 How to fix a cell with Fn4 in an Excel formula
03:53 Calculate age with year and fraction of a year in Excel =yearfrac()
05:21 Excel calculate age in years, months and days by joining functions with &
06:06 How to add descriptive text in a formula using inverted comas “”
Here are some more Excel tutorials:
Comments are most welcome, if you decide to try this please send us some feedback☺️ Why not visit our channel and browse through our other videos? http://www.youtube.com/c/AnnErasmus
Short Disclaimer/disclosure: My videos/articles are for informational and educational purposes only, you should consult with a professional before attempting anything seen or done in my videos/articles. Please read full disclaimer here: https://basiclowdown.com/category/disclaimer-disclosure/