Function: YEARFRAC

by | Sep 10, 2020 | Excel Formulas

YEARFRAC Function

The YEARFRAC is a Date & Time function that calculates and returns the fraction of the year between two days. The function calculates the fraction by the number of whole days in given day count basis. In this guide, we’re going to show you how to use the YEARFRAC function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

YEARFRAC Function's Syntax

YEARFRAC(start_date, end_date, [basis])

Arguments

start_date A date value that represents prior date.
end_date A date value that represents later date.
[basis] Optional. The type of day count basis.

Day Count basis

Basis Day count basis
0 or omitted US (NASD) 30/360 – All months are 30 days and the year is 360 days by US convention.
1 Actual/actual – Calculates by real-time day counts including leap years.
2 Actual/360 – A year is 360 days.
3 Actual/365 – A year is 365 days.
4 European 30/360 – All months are 30 days and the year is 360 days by European convention.

You can learn more about day conventions from here: https://en.wikipedia.org/wiki/Day_count_convention

YEARFRAC Function Example

Basic Usage

=YEARFRAC("1/7/2020","10/10/2020")

=YEARFRAC(C6,D6)

=YEARFRAC(DATE(2020,1,7),DATE(2020,10,10))

=YEARFRAC(C8,D8,E8)


YEARFRAC Function Basics

Calculating age with YEARFRAC Function

You can use YEARFRAC function along with INT and TODAY functions to calculate age by given birthday dates.

=INT(YEARFRAC(B18,TODAY()))

=INT(YEARFRAC(B19,TODAY()))


Calculating the percent of completed year

If you provide the previous year’s last day as start_date, you can find the percent of calculated year with the YEARFRAC function. DATE and YEAR functions can help to calculate previous year’s last day. You can find detailed information in How to calculate the percent of completed year.

=YEARFRAC(DATE(YEAR($B24),12,31),$B24,1)

=YEARFRAC(DATE(YEAR($B25),12,31),$B25,1)

=YEARFRAC(DATE(YEAR($B26),12,31),$B26,1)


YEARFRAC Function - Percent of Year

Download Workbook

Tips

  • Excel stores dates as serial numbers. Excel assumes the date 1/1/1900 as 1 and increases this number by 1 for each day. For example; 12/31/2018 is equal to 43465.
  • Because the calculation is based on whole days, Excel ignores the time part of start_date and end_date
  • If [basis] has decimal digits, the number will be truncated.

Issues

#NUM!

  • If [basis] is not between 0 and 4, the YEARFRAC function returns #NUM! error.

#VALUE!

  • If either start_date or end_date is not a valid date.