Age auto computation

Hi Mary, 

Thanks for your question! 

I’ve tested this scenario multiple times and found that the #VALUE! error when using the YEARFRAC function to calculate age is almost always caused by invalid date formatting — even if the cell looks like it contains a date. 

Your formula: =INT(YEARFRAC(A2, TODAY())) is correct. However, if cell A2 (the birthdate) is stored as text instead of a true date value, Excel cannot process it and will return a #VALUE! error. 

This usually happens when: 

• You manually type the date (e.g., 21/4/2019) but Excel doesn’t recognize it due to regional settings.
• The cell is formatted as Text.
• The separator used is incorrect (e.g., - or . instead of /).

How to fix it 

Option 1: Convert text to date using “Text to Columns” 

1. Select the column with birthdates.
2. Go to Data > Text to Columns.
3. Click Next > Next.
4. In the final step, choose Date: DMY (if your format is day/month/year).
5. Click Finish.

This forces Excel to convert the text into actual date values. 

Option 2: Use the DATE function 

If Excel won’t accept manual input, you can use: =DATE(2019,4,21)  

This creates a valid date that Excel can work with. 

Option 3: Check your regional settings 

Go to File > Options > Language, and make sure your Regional Format Settings match the way you enter dates (e.g., DD/MM/YYYY for many countries outside the US). 

Recommended formulas to calculate age 

Once your birthdate is correctly formatted, you can use: 

Option 1: YEARFRAC + INT: =INT(YEARFRAC(A2, TODAY()))  

Option 2: DATEDIF (more reliable): =DATEDIF(A2, TODAY(), "Y")  

Option 3: Simple year subtraction: =YEAR(TODAY()) - YEAR(A2)  

Note: This doesn’t account for whether the birthday has passed this year. 

Bonus tip: Check if the cell is a valid date 

=IF(ISNUMBER(A2), "Valid", "Invalid date")  

Let me know if you need help applying these steps. If this answer helps resolve your issue, please consider clicking “Mark as Answer” so others can benefit too! 

Wishing you success with your Excel sheet 
Best regards, 

Thu-D/Microsoft Community Support Specialist

Hi Mary, 
Just checking in to see if the above information was helpful. If you have any further updates on this issue, please feel free to post back. If you think my reply is helpful to you, please remember to mark it as an answer. Warm thanks.

Hi Mary, 
⚠️ This forum has moved! 

Outlook now supports Microsoft Q&A.

From July 17, new questions must be posted there.

Existing discussions about Microsoft Answers will remain accessible until July 21, after which customers will be redirected to Q&A.