Comments on: How to Calculate Age in Google Sheets (2 Easy Methods)

By: jmarkus

jmarkus — Fri, 23 Feb 2024 12:46:12 +0000

In reply to Brad.

This is actually a really weird story, but I’ve got it figured out.

The first argument in YEARFRAC calls the date in cell B1. However, dates in Google Sheets (like dates in Excel) are actually formatted as numbers.

To determine the number, Google Sheets calculates the date in relation to December 30, 1899. Days before this will appear as negative numbers, and negative numbers aren’t allowed in the YEARFRAC function’s first argument.

You can get around the issue by using the other formula I mentioned in the video. It’s =DATEDIF(B1,B2,”Y”)

That gives you the same result you’d expect from the INT version of your YEARFRAC formula.

Thanks for the question!

By: Brad

Brad — Fri, 22 Jul 2022 17:24:13 +0000

Works well, thank you!

I did encounter one issue though, I am using it to calculate how old someone would be if alive today. I used
=INT(YEARFRAC(B1,TODAY()))
and found it doesn’t work with dates prior to 1900! Any thoughts on that?

By: Brenda

Brenda — Wed, 08 Jun 2022 12:56:09 +0000

Works a treat – so happy to have this up and running. I got caught out as the format of the cell was incorrectly set, but a quick change to Automatic/Numeric did the trick> Thank you

By: Andrei

Andrei — Sat, 04 Jun 2022 02:49:10 +0000

In reply to Mr. Kapes, Interactive Media Teacher. The TODAY function doesn't calculate the date of birth, it gets the current date. That is a mistake in the text.

By: Vinay Paleja

Vinay Paleja — Fri, 08 Oct 2021 14:50:19 +0000

This is a very helpful formula. I have a few add-ons after my trial and error attempts:

1. To calculate the age on a particular date instead of using the “TODAY” option, mention the specific date within double quotes.

e.g. To calculate the age as on 12/31/2030 where DOB is 01/01/1950 use the formula =DATEDIF(“01/01/1950″,”12/31/2030″,”Y”). Note: The date format is set as “mm/dd/yyyy”, however it can be changed from the cell format option ans used accordingly in the formula.

2. I believe the field where age is calculated should be set as either “Automatic” or “Numeric”.

By: qazi

qazi — Wed, 24 Mar 2021 08:01:33 +0000

nice and helpful

By: Mabelle

Mabelle — Mon, 01 Feb 2021 05:13:55 +0000

Thank you so much! this really helped me a lot.

By: Mr. Kapes, Interactive Media Teacher

Mr. Kapes, Interactive Media Teacher — Thu, 21 Jan 2021 18:47:55 +0000

The key to all of this is making sure that the TODAY function to get the date of birth in cell B2 is used.

=(today())

I suspect that most people breeze through all of the instructions (just as I did) and miss this VERY important point!