How to Compute Age in Google Sheets


I don’t know about you, but I do get pretty annoyed if I need to divert my attention to any other device like my phone or a calculator for looking something up or calculating something really quick. It feels distracting and becomes difficult to focus on what we are working on.

Age is just one of those things that you need really frequently and mostly need to manually calculate it especially when working on spreadsheets. Thankfully, Google Sheets has two great functions to help us with the task and do it for us.

The two functions, DATEDIF and YEARFRAC, work in similar ways but the former is a little more flexible in terms of the output returned.

The DATEDIF function

This function lets you have more freedom in choosing the output format based on the syntax you use.

Syntax

The syntax looks like this: =DATEDIF(start_date, end_date, unit) where start_date is the date of birth of the individual. The age will be calculated as of the end_date where today’s date is most commonly used. Note that both of these can also be a reference to the cell address containing the actual date.

The unit determines the output format. Available formats are: “Y”, “M”, “D”, “YM”, “YD” or “MD”. Their meanings are as follows:

  • Y- The number of full elapsed years are returned.
  • M- The number of fully elapsed months are returned.
  • D- The number of fully elapsed days are returned.
  • YM- The number of additional elapsed months are returned after the number of fully elapsed years.
  • YD- The number of additional fully elapsed days are returned after the number of fully elapsed years.
  • MD- The number of additional fully elapsed days are returned after the number of fully elapsed months.

Example

Let’s look at the practical use of the function. Click on the cell you want to return the age and right the function as mentioned above with the desired output format. In this example I have used the TODAY() function to enter today’s date, you can check out our tutorial on that if you have any difficulties.

The YEARFRAC function

This one is just a simple function that returns the age in years.

Syntax

The syntax looks like this: =YEARFRAC(start_date, end_date) where start_date and end_date are exactly the same as DATEDIF function.

Example of the YEARFRAC function

And that was all for this article! Hope it helped you to learn something new today.

Recent Content