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

=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”

1. Kris says:

Could you show an example spreadsheet on how to calculate age?