How to Use the YEARFRAC Function in Excel
This document provides a comprehensive explanation of how to use the YEARFRAC function in both Microsoft Excel and Google Sheets. This function is designed to calculate the portion of the year expressed as the total number of whole days between two specified dates.
Basic Syntax
The syntax for the YEARFRAC function is consistent across both Excel and Google Sheets:
YEARFRAC(start_date, end_date, [basis])start_date: The initial date of the period for which you wish to calculate the year fraction.end_date: The final date of the period for which you wish to calculate the year fraction.basis(optional): This parameter defines the day count convention to be used in the calculation. If not specified, the default value is 0.
Practical Examples
Calculating Age
Consider a scenario where you have a list of birth dates in column A and you need to determine each person’s age as of today’s date employing the YEARFRAC function:
| Birth Date | Age |
|---|---|
| A2: 01/15/1990 | =YEARFRAC(A2, TODAY(), 1) * 100 |
Proportional Investment Return
Suppose you invested in a fund on March 15, 2020, and sold your shares on November 20, 2021. You aim to quantify the proportion of the year during which the investment was held:
| Investment Start | Investment End | Days Held | Proportional Time Held |
|---|---|---|---|
| B2: 03/15/2020 | C2: 11/20/2021 | =DAYS(C2, B2) | =YEARFRAC(B2, C2, 1) |
The YEARFRAC function is highly adaptable and can be effectively utilized in a variety of scenarios that require calculations based on dates.
More information: https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8