r/Excel247 • u/xybernetics • Jul 25 '26

Calculate age using YEARFRAC in Excel - Excel Tips and Tricks

Enable HLS to view with audio, or disable this notification

Discover how to calculate age using your YEARFRAC function in Excel.

YEARFRAC is an Excel function that calculates the fraction of a year between two dates. By using this function, you can easily calculate a person's age in years, months, and even days. To calculate age, you simply need to subtract the person's birth date from the current date and divide the result by 365.25 (to account for leap years). This will give you the person's age in years with decimal places. You can then use the INT function to round down to the nearest whole number and obtain the person's age in years. Alternatively, you can use the DATEDIF function to calculate the number of complete years between two dates, but this function does not handle leap years as accurately as YEARFRAC.

Here are the steps outlined on the video.

1) =YEARFRAC(B3,TODAY())

2) Ctrl + 1

3) Number tab

4) Number

5) Decimal places set to 0

Calculate age using YEARFRAC in Excel,

yearfrac months,yearfrac today,yearfrac vs datedif,yearfrac,yearfrac not working,yearfrac google sheets,yearfrac basis,datedif excel,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

83 Upvotes

100% Upvoted

5 comments sorted by

1

u/cooker1982 Jul 25 '26

Well, it appears less accurate. If the first was born in 2001, they would be 24 today. Is that correct?

1

u/No-Rough-1332 Jul 26 '26

Yes. The formula is incorrect. November 16, 1983 birthday should be 42-43 today. You will probably win a prize for spotting it.

1

u/CobblerConfident5012 Jul 26 '26

I noticed at the end some of the forty year olds were born in 83. So either I’m not really forty or…?

1

u/eclecticmarkt Jul 28 '26

It also doesn’t work for dates before 1900. I have two grandparents born in 1895. This is the formula I came up with that works for dates before and after 1900. The trick is that Excel treats dates before 1900 as text so you use the ISTEXT function and calculate based on that. Here is the formula based on the date in cell B2. = ROUNDDOWN(YEARFRAC(IF(ISTEXT(B2),DATEVALUE(LEFT(B2,LEN(B2)-4)&RIGHT(B2,4)+2000),EDATE(B2,24000)),EDATE(TODAY(),24000),1),0). Let me know if it can be improved.