Table of Contents
The YEARFRAC function in Excel is a useful tool for calculating the number of years between two given dates. It can be used to determine the fractional number of years between two dates, which can be helpful for financial and time-based calculations. To use the YEARFRAC function, simply input the start and end dates in the correct format and the function will return the number of years between them. This function can be particularly useful for analyzing trends and making projections based on historical data. It is a straightforward and efficient way to calculate the passage of time in Excel.
This article describes the formula syntax and usage of the YEARFRAC function in Microsoft Excel.
Description
YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year’s benefits, or obligations to assign to a specific term.
Syntax
YEARFRAC(start_date, end_date, [basis])
The YEARFRAC function syntax has the following arguments:
Start_date Required. A date that represents the start date.
End_date Required. A date that represents the end date.
Basis Optional. The type of day count basis to use.
Basis | Day count basis |
|---|---|
0 or omitted | US (NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
Important:
Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2018,5,23) for the 23rd day of May, 2018. Problems can occur if dates are entered as text.
The YEARFRAC function may return an incorrect result when using the US (NASD) 30/360 basis, and the start_date is the last day in February.
Remarks
Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2018 is serial number 43101 because it is 43,101 days after January 1, 1900.
All arguments are truncated to integers.
If start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
If basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
Cite this article
stats writer (2024). How do I use the YEARFRAC function in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-use-the-yearfrac-function-in-excel/
stats writer. "How do I use the YEARFRAC function in Excel?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-do-i-use-the-yearfrac-function-in-excel/.
stats writer. "How do I use the YEARFRAC function in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-use-the-yearfrac-function-in-excel/.
stats writer (2024) 'How do I use the YEARFRAC function in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-use-the-yearfrac-function-in-excel/.
[1] stats writer, "How do I use the YEARFRAC function in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How do I use the YEARFRAC function in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.