YEARFRAC Function (Functions in Excel)

Start Date	End Date	Fraction
1-Jan-98	1-Apr-98	0.25	=YEARFRAC(C4,D4)
1-Jan-98	31-Dec-98	1	=YEARFRAC(C5,D5)
1-Jan-98	1-Apr-98	25%	=YEARFRAC(C6,D6)

What Does It Do?
This function calculates the difference between two dates and expresses the result
as a decimal fraction.
Syntax
=YEARFRAC(StartDate,EndData,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 0r 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.

Start	End	Pro Rata Salary	Actual Salary
1-Jan-98	31-Dec-98	£12,000	£12,000	=YEARFRAC(B32,C32+1,4)*D32
1-Jan-98	31-Mar-98	£12,000	£3,000	=YEARFRAC(B33,C33+1,4)*D33
1-Jan-98	30-Jun-98	£12,000	£6,000	=YEARFRAC(B34,C34+1,4)*D34

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.

Related Posts by Categories