Calculate age from date of birth in Google Docs (Spreadsheet)



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.

10 thoughts on “Calculate age from date of birth in Google Docs (Spreadsheet)”

  1. 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

  2. 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.

  3. 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

  4. 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.

  5. 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)

  6. 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….

  7. @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

Leave a Reply

Your email address will not be published. Required fields are marked *