ID EN
…
#Date and time

YEARFRAC

Excel Functions

Get the fraction of a year between two dates

Syntax

EXCEL 
=YEARFRAC(start_date, end_date, [basis])

Arguments

Parameter Description
start_date The start date.
end_date The end date.
basis [optional] The type of day count basis to use (see below).

Return Value

A decimal number

Details

YEARFRAC returns a decimal number representing years between two dates. For example: Although the generic syntax for YEARFRAC shows the start date followed by the end date, you can provide the dates in any order with the same result. For example: YEARFRAC uses whole days between two dates to calculate the fraction of a year as a decimal number. The YEARFRAC function accepts an optional argument called "basis" that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The table below summarizes the available options: Note that basis 0 (the default) and basis 4 both operate based on a 360-day year, but they handle the last day of the month differently.

Examples

Example 1

YEARFRAC returns a decimal number representing years between two dates. For example:

EXCEL 
=YEARFRAC("1-Jan-2019","1-Jan-2020") // returns 1
=YEARFRAC("1-Jan-2019","1-Jul-2020") // returns 1.5
=YEARFRAC("1-Jan-2019","1-Jan-2021") // returns 2
Example 2

Although the generic syntax for YEARFRAC shows the start date followed by the end date, you can provide the dates in any order with the same result. F

EXCEL 
=YEARFRAC("1-Jan-2000","1-Jan-2019") // returns 19
=YEARFRAC("1-Jan-2019","1-Jan-2000") // returns 19
Examples

With a start date in cell A1, and an end date in cell B1, the YEARFRAC will return years between the two dates as a decimal number:

EXCEL 
=YEARFRAC(A1,B1) // years between two dates
Examples

To get a whole number only (not rounded), you can use the INT function like this:

EXCEL 
=INT(YEARFRAC(A1,B1)) // whole number only, discard decimal
Examples

To get current age based on a birthdate, you can use a formula like this:

EXCEL 
=INT(YEARFRAC(birthdate,TODAY())) // age from birthdate
Examples

To get the percentage of the current year complete, you can use YEARFRAC like this:

EXCEL 
=YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()) // % year complete

See Also

DAYS360 DATEDIF