Date & Time

YEARFRAC Function in Excel

Returns the fraction of the year represented by the number of days between two dates.

Syntax

  • =YEARFRAC(start_date, end_date, [basis])

Arguments

  • start_date (required): Start date
  • end_date (required): End date
  • basis (optional): Day count basis (0-4)

Examples

  • =YEARFRAC("1/1/2024", "7/1/2024") - Half year - Result: 0.5

Working with dates in YEARFRAC

  • Format cells as Date after formulas — serial numbers are normal underneath.
  • Use [TODAY](/functions/today/) and [NETWORKDAYS](/functions/networkdays/) for business calendars.
  • Guide: [Excel date functions](/guides/excel-date-functions-guide/).

Common errors

  • Basis affects calculation method

Use cases

  • Interest calculations
  • Age in years
  • Prorated amounts

Frequently asked questions

  • What do the different basis values mean? 0=US 30/360, 1=Actual/Actual, 2=Actual/360, 3=Actual/365, 4=European 30/360. For most business uses, 1 (Actual/Actual) is most accurate. Financial instruments often specify which basis to use.
  • How do I calculate exact age in years? Use =YEARFRAC(BirthDate, TODAY(), 1) for decimal years, or =INT(YEARFRAC(BirthDate, TODAY(), 1)) for whole years. DATEDIF is another option: =DATEDIF(BirthDate, TODAY(), "Y").

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use YEARFRAC

  • Interest calculations — common Date & Time scenario for YEARFRAC.
  • Age in years — common Date & Time scenario for YEARFRAC.
  • Prorated amounts — common Date & Time scenario for YEARFRAC.

YEARFRAC in the Date & Time category

  • Browse all Date & Time functions at /categories/date-time/ for related formulas.
  • YEARFRAC syntax: =YEARFRAC(start_date, end_date, [basis])
  • start_date (required): Start date
  • end_date (required): End date
  • basis (optional): Day count basis (0-4)
  • Confirm YEARFRAC arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm YEARFRAC arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • DATEDIF (/functions/datedif/): Calculates the number of days, months, or years between two dates.
  • DAYS (/functions/days/): Returns the number of days between two dates.
  • DAYS360 (/functions/days360/): Returns the number of days between two dates based on a 360-day year.

Errors to watch for

  • Basis affects calculation method — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter YEARFRAC on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

YEARFRAC worked examples to copy

  • =YEARFRAC("1/1/2024", "7/1/2024") — Half year. Expected result: 0.5.

YEARFRAC reference summary for crawlers and offline review

  • YEARFRAC belongs to the Date & Time category in Excel. Returns the fraction of the year represented by the number of days between two dates.
  • Full syntax: =YEARFRAC(start_date, end_date, [basis]). Open /functions/yearfrac/ for parameters, FAQs, and related pages.
  • Common mistakes: Basis affects calculation method
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.