Excel YEARFRAC Function
last modified October 5, 2026
The YEARFRAC function calculates the fraction of a year between two
dates. It is essential for financial calculations, interest accruals, and age
computations, and it is the function to reach for when you need a decimal number
of years rather than a whole count. This tutorial provides a comprehensive guide
to using YEARFRAC with detailed examples. You'll learn its syntax,
all five day-count bases, the 30/360 rules, practical applications, and the
errors that can trip you up.
YEARFRAC Function Basics
The YEARFRAC function returns the fraction of a year represented
by the number of days between two dates. Different day-count conventions produce
different results, so the optional basis argument controls how days
and years are counted. If basis is omitted, Excel uses basis 0, the
US (NASD) 30/360 convention.
| Component | Description |
|---|---|
| Function Name | YEARFRAC (Year Fraction) |
| Syntax | =YEARFRAC(start_date, end_date, [basis]) |
| Arguments | start_date, end_date, basis (optional) |
| Return Value | Fraction of a year between the dates, as a decimal |
This table breaks down the essential components of the YEARFRAC
function. The first two arguments are required; only basis is
optional. All three arguments are truncated to integers, so any time portion of
a date is discarded before the calculation begins.
| Argument | Meaning |
|---|---|
| start_date | The beginning date of the period. Supply a real date with
DATE, a cell reference, or a date returned by another
function. |
| end_date | The ending date of the period, supplied the same way as
start_date. |
| basis | Optional. The day-count convention, from 0 to 4. The default is 0. |
Dates should be entered with the DATE function or as references to
cells that already contain dates. A date typed as text can be read incorrectly
or rejected with a #VALUE! error. A basis outside the
range 0 to 4 returns #NUM!.
Day Count Basis Reference
YEARFRAC supports five day-count conventions. The basis decides
both how many days are counted between the dates and how many days are assumed
to be in a year.
| Basis | Day-count convention | Days per year | Notes |
|---|---|---|---|
| 0 or omitted | US (NASD) 30/360 | 360 | Default; some end-of-month adjustments |
| 1 | Actual/actual | 365, 366, or an average | Leap-year aware |
| 2 | Actual/360 | 360 | Common for money-market instruments |
| 3 | Actual/365 | 365 | Common in some bond markets |
| 4 | European 30/360 | 360 | Every 31st treated as 30 |
The table lists every basis YEARFRAC accepts. Bases 0 and 4 both
assume 360-day years but differ in how they handle end-of-month dates; basis 1
is the only convention that accounts for leap years.
The 30/360 Day-Count Rules
Bases 0 and 4 count every month as exactly 30 days and every year as exactly 360
days. Before applying the formula, Excel adjusts end-of-month dates, and the two
conventions adjust them differently. The adjusted day count is
(Y2-Y1)*360 + (M2-M1)*30 + (D2-D1), where Y, M, and D are the year,
month, and day of the two dates.
| US (NASD) 30/360, basis 0 | European 30/360, basis 4 |
|---|---|
| If the start day is 31, it becomes 30. | If the start day is 31, it becomes 30. |
| If the end day is 31 and the adjusted start day is 30, the end day becomes 30. Otherwise the end day stays 31. | If the end day is 31, it always becomes 30. |
| If the start date is the last day of February, it is treated as day 30; if the end date is also the last day of February, it too is treated as day 30. | No special February rule. |
The difference between the two conventions shows up when the start day is earlier than 30 and the end day is 31. For January 15 to March 31, 2025, basis 0 keeps the end day at 31 and counts 76 days, while basis 4 lowers it to 30 and counts 75 days.
| Start Date | End Date | Basis | Adjusted days | Result |
|---|---|---|---|---|
| 1/15/2025 | 3/31/2025 | 0 | 76 | 0.21111111 |
| 1/15/2025 | 3/31/2025 | 4 | 75 | 0.20833333 |
=YEARFRAC("2025-01-15", "2025-03-31", 0) ' 0.21111111
=YEARFRAC("2025-01-15", "2025-03-31", 4) ' 0.20833333
Microsoft warns that the US (NASD) 30/360 basis can return an incorrect result when the start date is the last day of February. Treat February month-end dates with care when you use basis 0, and test the specific dates that matter to you.
Basic YEARFRAC Example
This example demonstrates the simplest use of YEARFRAC with the default basis.
=YEARFRAC("2025-01-01", "2025-07-01")
This formula calculates the fraction of a year between January 1 and July 1, 2025. The default basis 0 counts six 30-day months, so the result is exactly 0.5. The same period spans 181 actual days, which the other bases divide by 360 or 365.
YEARFRAC with Different Basis Methods
The same date pair can produce five different answers depending on the basis. The table below evaluates January 1, 2025 to July 1, 2025 with every convention.
| Basis | Convention | Days counted | Result |
|---|---|---|---|
| 0 | US (NASD) 30/360 | 180 | 0.5 |
| 1 | Actual/actual | 181 | 0.49589041 |
| 2 | Actual/360 | 181 | 0.50277778 |
| 3 | Actual/365 | 181 | 0.49589041 |
| 4 | European 30/360 | 180 | 0.5 |
The table shows all available basis methods for the same dates. In 2025, a non-leap year, basis 1 and basis 3 agree at 0.49589041 because both divide the 181 actual days by 365. Bases 0 and 4 agree at exactly 0.5 because neither date falls on the 31st. Basis 2 gives the largest value, 0.50277778, because it divides by the shorter 360-day year.
=YEARFRAC("2025-01-01", "2025-07-01", 1) ' 0.49589041
=YEARFRAC("2025-01-01", "2025-07-01", 2) ' 0.50277778
=YEARFRAC("2025-01-01", "2025-07-01", 3) ' 0.49589041
YEARFRAC for Age Calculation
YEARFRAC can calculate a precise age by comparing a birth date to a
current date. Basis 1 is the usual choice because it accounts for leap years.
| A | B |
|---|---|
| Birth Date | 1990-05-15 |
| Current Date | 2025-04-04 |
| Age | =YEARFRAC(B1,B2,1) |
The table demonstrates using YEARFRAC to calculate age in years.
With basis 1 the result is about 34.89, a fractional value that includes the
part of the current year already lived. Wrap the call in INT to get
the whole-number age: =INT(YEARFRAC(B1,B2,1)) returns 34.
=YEARFRAC(B1, TODAY(), 1)
This dynamic formula calculates current age using TODAY() as the
end date. Because TODAY() is volatile, the result updates every time
the workbook recalculates.
YEARFRAC for Financial Calculations
YEARFRAC is commonly used in finance to prorate accrued interest
between payment periods.
| A | B |
|---|---|
| Last Payment | 2025-01-01 |
| Next Payment | 2025-07-01 |
| Current Date | 2025-04-04 |
| Accrued Days | =YEARFRAC(B1,B3,3)*365 |
The table shows how to recover the accrued days from YEARFRAC by
multiplying the actual/365 fraction back by 365. From January 1 to April 4, 2025
there are 93 days, so the fraction is 0.25479452 and the accrued days are 93.
=YEARFRAC(B1,B3,3)*10000*0.05
This formula calculates accrued interest on a $10,000 bond with a 5% annual rate. It multiplies the year fraction (0.25479452) by the principal and the rate, returning about $127.40. Basis 3 (actual/365) is a common convention for this kind of proration.
YEARFRAC for Project Duration
YEARFRAC can express project progress as a percentage by comparing
elapsed time to total duration.
| A | B |
|---|---|
| Start Date | 2025-01-01 |
| End Date | 2025-12-31 |
| Current Date | 2025-04-04 |
| Progress | =YEARFRAC(B1,B3,1)/YEARFRAC(B1,B2,1) |
The table demonstrates project progress calculation. The elapsed period, January 1 to April 4, is 93 days, and the full project runs 364 days to December 31. The ratio is about 0.2555, or 25.5% complete.
=YEARFRAC(B1,B3,1)/YEARFRAC(B1,B2,1)
Dividing one YEARFRAC by another cancels the common denominator, so
the result is simply the ratio of elapsed days to total days. Using the same
basis for both calls is what keeps the comparison valid.
YEARFRAC with Cell References
YEARFRAC works with cell references containing dates, making it
dynamic and reusable.
| A | B |
|---|---|
| Hire Date | 2020-06-15 |
| Current Date | =TODAY() |
| Tenure | =YEARFRAC(B1,B2,1) |
The table shows employee tenure calculation using cell references. As with the
age example, basis 1 gives a fractional result, and INT trims it to
whole years.
=YEARFRAC(B1, TODAY(), 1)
This dynamic formula calculates current tenure in years. Using
TODAY() ensures the result is always up to date without manual date
entry.
YEARFRAC vs DATEDIF and DAYS
Several Excel functions measure the distance between dates, and choosing the right one depends on whether you need a fraction, a whole interval, or a plain day count.
| Function | Returns | Best for |
|---|---|---|
YEARFRAC |
A decimal number of years | Prorating interest, fractional age, progress percentages |
DATEDIF |
Whole years, months, or days | Completed age or tenure, readable duration strings |
DAYS |
The number of calendar days | A raw day count between two dates |
DAYS(end_date, start_date) simply subtracts the serial numbers and
returns a whole day count, with no year fraction involved.
DATEDIF rounds down to completed intervals, so it is the natural
partner for whole-year ages. YEARFRAC is the only one of the three
that returns a fraction of a year and lets you choose a day-count convention, so
use it whenever the decimal part carries meaning.
=YEARFRAC("2025-01-01", "2025-07-01", 1) ' 0.49589041
=DATEDIF("2025-01-01", "2025-07-01", "m") ' 6
=DAYS("2025-07-01", "2025-01-01") ' 181
YEARFRAC Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| A date is text that Excel cannot parse | #VALUE! error | Enter real dates or convert text with DATEVALUE |
| basis is text that is not numeric | #VALUE! error | Pass a number from 0 to 4, not a word |
| basis is negative or greater than 4 | #NUM! error | Use a basis from 0 to 4 |
| Using the 30/360 US basis near a February month end | A slightly wrong fraction | Switch to basis 1 or test the boundary dates |
| Result looks like a date or shows too many decimals | A serial number or a long decimal | Format the cell as General or apply a number format |
The #VALUE! and #NUM! errors are the easiest to
diagnose: they almost always come from a text date or an out-of-range basis.
Wrap the call in IFERROR if you want a friendlier message:
=IFERROR(YEARFRAC(A2,B2,1),"Check the dates").
The YEARFRAC function turns two dates into a decimal number of
years, which makes it the right tool for prorating interest, measuring
fractional age and tenure, and tracking project progress. Its power comes from
the basis argument: the 30/360 conventions give clean financial
fractions, while actual/actual and actual/365 follow the calendar more closely.
Remember that the default basis is 0, that only basis 1 is leap-year aware, and
that February month ends can bend the US 30/360 result. Master these details and
YEARFRAC becomes a dependable part of your date toolkit.
Author
List all Excel Formulas.