Better formula for calculating age in spreadsheet

I came across a formula for calculating age based on date of birth (DOB) in Excel (or Google Docs, Libre Office, Open Office, etc). The formula is posted all over the place on the internet. It works great, but has one fatal flaw…

If the date of birth is blank in the spreadsheet, the math gives a strange answer. For example, my “blank” date of birth fields were giving an age of 115. Fortunately, this was an easy fix. We remedy the situation by adding some if logic.

My spreadsheet looked at C2 for the date of birth (adjust your cells accordingly). The original formula I found on the internet was this (DO NOT USE):

=DATEDIF(C2,TODAY(),“Y”)

My new formula (Blanks out cell if date of birth is unknown) (USE THIS ONE!!!):

=IF(C2,DATEDIF(C2,TODAY(),“Y”),“”)

So basically, I added an ‘if’ statement. Notice the ‘C2’ as the first parameter. This is the ‘test’ in the if condition. Therefore, the condition reads as follows: If C2 exist, do the datediff, otherwise put in a blank.

One thought on “Better formula for calculating age in spreadsheet

Leave a Reply