YEARFRAC - Calculate the difference between 2 dates

YEARFRAC - Calculate the difference between 2 dates and express the result as a decimal fraction

Syntax

=YEARFRAC(StartDate,EndDate,Basis)

Basis: Defines the calendar system to be used in the function.

              0 : or omitted USA style 30 days per month divided by 360.

              1 : 29 or 30 or 31 days per month divided by 365.

              2 : 29 or 30 or 31 days per month divided by 360.

              3 : 29 or 30 or 31 days per month divided by 365.

              4 : European 29 or 30 or 31 days divided by 360.

Formatting

The result will be shown as a decimal fraction, but can be formatted as a percent.

Example

The following table was used by a company which hired people on short term contracts for a part of the year. The Pro Rata Salary which represents the annual salary is entered. The Start and End dates of the contract are entered. The =YEARFRAC() function is used to calculate Actual Salary for the portion of the year.

	Row B	Row C	Row D	Row E	Row F
	Start Date	End Date	Actual Salary	Prorata Salary
Col.  3	1-Jan-2014	31-Dec-2014	Rs. 12000	Rs. 12000	=YEARFRAC(B3,C3+1,1)*D3
Col. 4	1-Jan-2014	31-Mar-2014	Rs. 12000	Rs. 3000	=YEARFRAC(B4,C4+1,1)*D4
Col. 5	1-Jan-2014	30-Jun-2014	Rs. 12000	Rs. 6000	=YEARFRAC(B5,C5+1,1)*D5

Note

The extra 1 has been added to the End date to compensate for the fact that the =YEARFRAC() function calculates from the Start date up to, but not including, the End date.