The `YEARFRAC` function in Excel is used to calculate the fraction of a year represented by the number of whole days between two dates. This can be useful for calculating age, the duration of a project, or interest calculations on loans over irregular periods.
Syntax of the `YEARFRAC` function:
YEARFRAC(start_date, end_date, [basis])
- `start_date`: This is required. The starting date from which you want to calculate the fraction of the year.
- `end_date`: This is required. The ending date up to which you want to calculate the fraction of the year.
- `basis`: This is optional. It specifies the day count basis to use. If omitted, it defaults to zero. Each basis has different assumptions for the calculations, and here are the options:
- `0` or omitted: US (NASD) 30/360
- `1`: Actual/actual
- `2`: Actual/360
- `3`: Actual/365
- `4`: European 30/360
Example Usage:
Let’s say you want to find out how many years are between January 1, 2022, and March 15, 2023.
=YEARFRAC("2022-01-01", "2023-03-15")
- This formula assumes the default `basis 0` (US 30/360) and calculates the fraction of the year between the two dates.
If you need a different basis, you can specify it like this:
=YEARFRAC("2022-01-01", "2023-03-15", 1)
- With `basis 1`, it uses the `Actual/actual` day counting method, which may provide a more precise calculation depending on the year and leap years.
Important Tips:
- Ensure that the dates are in a format recognized by Excel, or use cell references containing valid date data.
- When involving financial calculations, keep in mind that different institutions or calculations might require a specific day count convention, so select your basis accordingly.
- Mixing up the `start_date` and `end_date` can lead to negative results or errors, so ensure they are correctly ordered.
Unlock Your Potential
Excel
Access
Power BI
