I. Intro
You can use the YEARFRAC function to calculate the fractional years between two dates.
II. About the function
- Formula: =YEARFRAC(start date, end date, [basis]).
- Parameters:
- Start date (required): The start date of the period. It must be one of the following: a reference to a cell containing a date, a function returning a value in a date format, or an integer.
- End date (required): The end date of the period. It must be one of the following: a reference to a cell containing a date, a function returning a value in a date format, or an integer.
- Basis (optional): The calculation method to be used, as explained below.
- Example:
- In a cell enter =YEARFRAC(DATE(2021,7,16),DATE(2021,7,24),1).
- The formula will return the fractional year between these two dates.
- Calculation method: The actual number of days in the month/year.
III. Steps
- Select the cell and click Formula in the toolbar, select All functions, and search for YEARFRAC. You can also directly enter =YEARFRAC in a cell.
- In a cell, enter =YEARFRAC("2021/1/1","2021/6/30"). You can use this function in combination with the DATE function to convert numbers to dates like this: =YEARFRAC(DATE(2021,1,1),DATE(2021,6,30),1). This will return the fractional year between these dates. Calculation method: The actual number of days in the months (January to June)/year (2021).
- Press Enter to display the result. You can change the decimal value to percentages by changing the cell format in the toolbar.
250px|700px|reset