Function: YEARFRAC()

🔍 Purpose:

The YEARFRAC() function calculates the exact difference between two dates in years, including fractions (decimal parts).
It’s extremely useful when you want to know how many full and partial years exist between two dates — for example, employee experience, student course duration, loan age, or age calculation.

🧮 Syntax: =YEARFRAC(start_date, end_date, [basis])


Parameters:

• start_date → The beginning date.

• end_date → The ending date.

• [basis] (optional) → Defines how the year is calculated (days per year basis).

📘 Basis Options Table:

💡 Usually, you can use 1 for real-world accuracy in India, as it counts actual days between two dates.

Example :

🧠 How It Works:

Excel counts days between the two dates and divides them by the total days in a year, depending on the selected basis.
This gives you an accurate decimal value of years — ideal for financial and HR calculations.

🧾 Example – Employee Experience

✅ HR can use this to display exact experience up to months or even days.

🎯 5 Real-Life Project Scenarios (All Different)

🏫 Project 1: Student Course Duration (Education Scenario)

Objective:
Calculate how long each student has been enrolled in their course.

🎯 Goal: Track total study period (e.g., 2.5 years) for academic reports.

💼 Project 2: Employee Service Record (HR Scenario)

Objective:
Calculate how many years each employee has completed in the company.

💡 Use the result for promotion eligibility or loyalty awards.

🏦 Project 3: Loan Tenure Calculation (Finance Scenario)

Objective:
Find how many years have passed since each customer’s loan started.

🏦 Goal: Determine loan maturity, calculate interest period, or review overdue accounts.

🧾 Project 4: Insurance Policy Age (Insurance Scenario)

Objective:
Calculate how old each policy is since its issue date.

📅 Goal: To identify which policies are eligible for renewal or maturity.

🚗 Project 5: Vehicle Age Tracker (Automobile Scenario)

Objective:
Find how old each vehicle is since its purchase or registration.

🚘 Goal: Used by insurance or RTOs to assess vehicle depreciation or resale eligibility.

🧮 Extra Tip – Age Calculation in Years (with Fractions or Rounding):

• Exact Decimal:

   

  =YEARFRAC(DOB, TODAY())


  → Returns 25.76 (years)

• Rounded Whole Years (Age):

   

  =INT(YEARFRAC(DOB, TODAY()))


  → Returns 25 years

🧠 Key Insights:

✅ YEARFRAC() gives precise year differences (useful for financial or HR reporting).
✅ You can multiply by 12 to get the difference in months:

✅ Use with TODAY() for live updates — no need to change dates manually.
✅ Great for tracking project duration, insurance period, or policy age.