| Function category | Date and time functions |
| Volatility | Non-volatile |
What does YEARFRAC do?
The YEARFRAC function in Excel returns the fraction of a year between two dates. This is especially useful in financial calculations where you need to account for how much time has passed between two events as a portion of a year: for example, when calculating interest, depreciation, or return rates.
Unlike simply counting days between dates, YEARFRAC allows you to accurately account for how days in a year are calculated — using actual days, a 360-day year, or other methods.
YEARFRAC function syntax
=YEARFRAC(start_date, end_date, [basis])| A | B | C | |
|---|---|---|---|
| 1 | Basis | Method | YEARFRAC |
| 2 | 0 | US 30/360 (default) | 0.5=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),A2) |
| 3 | 1 | Actual / actual | 0.4972678=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),A3) |
| 4 | 2 | Actual / 360 | 0.5055556=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),A4) |
| 5 | 3 | Actual / 365 | 0.4986301=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),A5) |
| 6 | 4 | European 30/360 | 0.5=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),A6) |
The period has 182 actual days. 30/360 counts it as 6 × 30 = 180 days of 360 → exactly 0.5. Actual/actual divides by 366 (2024 is a leap year), Actual/360 by 360, Actual/365 by 365.
- start_date — period start date
- end_date — period end date
- [basis] — (optional) method for calculating days in a year. Can take values from 0 to 4:
| Basis | Description |
|---|---|
| 0 | US (NASD) 30/360 |
| 1 | Actual days / actual year |
| 2 | Actual days / 360 |
| 3 | Actual days / 365 |
| 4 | European 30/360 |
YEARFRAC formula examples
- =YEARFRAC("01/01/2024", "07/01/2024") — returns 0.5 if using default basis
- =YEARFRAC("01/01/2024", "07/01/2024", 3) — returns 0.4986, since the year is considered 365 days
Good to know
- The order of the dates doesn’t matter: if the start date is later than the end date, YEARFRAC swaps them and still returns a positive number
- When working with financial models, it's important to specify the basis explicitly to avoid ambiguity
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Years (fraction) | Full years | Interest on $10,000 at 6% |
| 2 | 01/15/2024 | 10/22/2026 | 2.7694444=YEARFRAC(A2,B2) | 2=INT(YEARFRAC(A2,B2)) | 1661.67=10000*6%*C2 |
| 3 | 10/22/2026 | 01/15/2024 | 2.7694444=YEARFRAC(A3,B3) | 2=INT(YEARFRAC(A3,B3)) | 1661.67=10000*6%*C3 |
| 4 | 03/01/2026 | 09/01/2026 | 0.5=YEARFRAC(A4,B4) | 0=INT(YEARFRAC(A4,B4)) | 300.00=10000*6%*C4 |
Rows 2 and 3 are the same dates in reverse order — the result is the same positive number. INT gives full years (like DATEDIF with "Y"); multiplying the annual rate by the fraction gives the interest for the part of a year.
Applications
- Calculating interest on loans and credits when the rate is specified in annual percentage
- Determining the portion of depreciation for an asset used for less than a full year
- Financial modeling in investment projects
Other Date and Time functions in Excel
TIMEVALUE, TIME, YEAR, DATE, DATEVALUE, EDATE, DAY, WEEKDAY, EOMONTH, MONTH, MINUTE, WEEKNUM and ISOWEEKNUM, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.