YEARFRAC() function

Last update on August 19 2022 21:50:55 (UTC/GMT +8 hours)

YEARFRAC() function

This function is used to calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date).

Syntax:

YEARFRAC(start_date, end_date, [basis])

Parameters:

Name	Description	Data Type	Required/ Optional
Start_date	A date that represents the start date.	Date	Required
End_date	A date that represents the end date.	Date	Required
Basis	The type of day count basis to use.	Number	Optional

Here is the table below defines the basis value and the day count basis.

Basis	Day count basis
0 or omitted	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

The dates should be entered properly and better to use the DATE function, or as results of other formulas or functions.

Remarks:

Microsoft Excel stores date as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900.
when start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
when basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.

Example - 1:

The example below returns the fraction of the year between 4/1/2012 and 4/30/2012, omitting the Basis argument.

yearrac1

Example - 2:

The example below returns the fraction between 4/1/2012 and 4/30/2014 using the Actual/Actual basis argument. Because 2012 is a Leap year, it has a 366-day basis.

yearrac2

Example - 3:

The example below returns the fraction between 4/1/2012 and 4/30/2014, using the Actual/365 basis argument. Uses a 365-day basis.

Previous: Excel YEAR() function
Next: Excel Formulas - Excel Count