How to Use YEARFRAC Function in Excel + Tips (Video in Khmer)

Hello everyone! Today i will show you how to use YEARFRAC function in Microsoft Excel step by step. Very easy and useful formula for calculating age, work experience, or interest fraction between two dates! YEARFRAC is available in all Excel versions, Excel 365, and Excel for Web.

1. What is YEARFRAC Function in Excel?

The YEARFRAC function calculates the fraction of a year (in decimal numbers) between a start date and an end date.

Think of YEARFRAC like measuring date distance in year units:

• Exactly 6 months difference ➔ returns 0.5 years.
• Exactly 1 year and 3 months difference ➔ returns 1.25 years.
• Calculate exact age or work experience (e.g. 25.75 years old)!

2. 🛠️ Formula Syntax & Parameters

=YEARFRAC(start_date, end_date, [basis])

• start_date: Required. The starting date.
• end_date: Required. The ending date.
• [basis]: Optional. The type of day count logic to use:
  • 0 (or omit) = US (NASD) 30/360 (Default).
  • 1 = Actual/actual (Most accurate for exact real-world age & interest!).
  • 2 = Actual/360.
  • 3 = Actual/365.
  • 4 = European 30/360.

3. 📋 Practical Examples

Example 1: Calculate Exact Fraction of Year Between 2 Dates

Suppose cell A2 contains 2026-01-01 and cell B2 contains 2026-07-01 (6 months):

=YEARFRAC(A2, B2)

👉 Result: Returns 0.5 (half a year)!

Example 2: Calculate Exact Age from Birthday

Combine YEARFRAC with TODAY() to get exact age:

=YEARFRAC(A2, TODAY(), 1)

If birth date in A2 is 2000-05-15 and TODAY is 2026-09-21: 👉 Result: Returns 26.35 years!

💡 Want integer age only (no decimal)? Wrap inside INT():

=INT(YEARFRAC(A2, TODAY(), 1))

👉 Result: 26 years old!

Example 3: Calculate Employee Work Tenure / Experience

Calculate how many years an employee has worked at the company:

💡 Quick Tips for Using YEARFRAC Function

1. Use basis = 1 for Real-World Accuracy: Setting the 3rd parameter [basis] to 1 uses actual days divided by actual year length (365 or 366 for leap year), giving the most accurate results for age and bank interest calculations.
2. Order of Dates Does Not Matter: Whether start_date is earlier or later than end_date, YEARFRAC always returns a positive fraction!
3. Use INT() or ROUND() to Remove Decimals: If you need full whole years only (like age on passport), use =INT(YEARFRAC(start_date, end_date)).
4. Fix #VALUE! Error: If Excel returns #VALUE!, check if your date cells are real Excel date serial numbers and not formatted as plain text strings.

📚 Related Resources & Tutorials

• EDATE vs EOMONTH Function in Excel
• How to Use HOUR, MINUTE, SECOND in Excel
• Top 15 Basic Excel Formulas for Beginners ↗