Here is a simple way to calculate age from birthday in Google Spreadsheet (works in Microsoft Excel also)

Imagine that you have a column with date of births in it. Just create another column ‘Age’ and specify the following formula in each of its cell.

**=IF(E2,DATEDIF(E2,TODAY(),”Y”),””)**

E is the date of birth column.

Thanks to this post for showing it.

thanks :)

Thanks for the formula – however is there a more accurate expression that will calculate the age based also on day and month – the one I use in Excel doesn’t work on the Google Spreadhseet

The Age could be calculated using:

=YEAR(TODAY()) – YEAR(B3) – IF((TODAY() – DATE(YEAR(TODAY()); MONTH(B3); DAY(B3))) < 0; 1; 0)

Where:

B3 is the birthday date.

It works for leap years too.

If being accurate isn’t important it works with a leway of +_1 year as someone with a bithday Jan 1st is the same age as someone with a birthday in December 31st as long as they are the same year even though they are almost 1 year apart

Hi guys,

Try this to round down age number, as we usually do when we say our age.

=rounddown((TODAY() – A1) /365,1)

Cheers

Tyson Tyson’s formula works great, but in order to get it to work from a copy paste, I had to change the “long dash” that the web page uses, to a regular dash in Google docs.

Marcelo,

That worked great for me, thanks! I swapped the emdashes for hyphens as Simon said, and had to replace the semicolons with commas for my local (US).

Also, I tweaked it a bit to show an age based on the date shown in another column, rather than current day.

For example: =YEAR($E2)-YEAR(‘Formula Values’!$B$3)-IF(($E2-DATE(YEAR($E2), MONTH(‘Formula Values’!$B$3), DAY(‘Formula Values’!$B$3))) < 0, 1, 0)

How do you aplly this formula in googlesheets? The date of birth is formated like text and not number. In the Excel the formula =INT((TODAY()-E127)/365.25) works, but in google docs doesn’t!

I tried several times diferent formulas, but nothing works….

I found DATEDIF (date dif) to be a little easier to apply: https://support.google.com/docs/answer/6055612?hl=en

Basically, this simplifies Marcelo’s formula to be this:

`=DATEDIF(B3, TODAY(), "Y")`

to print out the difference in Years between the target date and today.

@Daniel Lemke

How did you get your formulae to work?

It would be handy to understand which parts of the Formulae reference which cells in the sheet.

For me, I suspect the Formulae needs to look as follows (however I result with a “-” figure eg.Someone is -37 yrs of age instead of 37) lol.

My hack of your formulae…

=YEAR($E2)-YEAR($A$2)-IF(($E2-DATE(YEAR($E2), MONTH($A$2), DAY($A$2))) < 0, 1, 0)

A2 is the timestamp column (eg: 12/01/2018 22:51:19 )

E2 is their provided DOB (eg: 11/09/1974 )

Result I'm getting: -44