Quick, clean, and to the point

Excel YEARFRAC Function

Excel YEARFRAC function

The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC to do things like calculate age with a birthdate.

Get the fraction of a year between two dates
Return value 
A decimal number
=YEARFRAC (start_date, end_date, [basis])
  • start_date - The start date.
  • end_date - The end date.
  • basis - [optional] The type of day count basis to use (see below).
Usage notes 

YEARFRAC calculates a decimal number representing the fraction of a year between two dates. Excel uses whole days between two dates to work out the fraction of a year as a decimal value. For example:

=YEARFRAC("1-Jan-2018","1-Jan-2019") // returns 1
=YEARFRAC("1-Jan-2018","1-Jul-2019") // returns 1.5

The YEARFRAC function has an optional argument 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 available options:

Basis Calculation Notes
0 (default) 30/360 US convention
1 actual/actual  
2 actual/360  
3 actual/365  
4 30/360 European convention

Note that basis 0 (the default) and basis 4 both operate based on a 360 day year, but they handle last day of month differently. With the US convention, when the start date is the last day of the month, it is set to the 30th day of the same month. When the end date is the last day of the month, and the start date < 30, the end date is set to the 1st of the next month, otherwise the end date is set to the 30th of the same month.

With the European convention, start dates and end dates equal to the 31st of a month are set to the 30th of the same month.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas