Sunday, February 19, 2012

How to Use Excel's YEARFRAC Function


1. Install the Analysis ToolPak, if needed. If YEARFRAC returns the #NAME? error value, select Add-Ins from the Tools menu. Mark the checkbox next to Analysis ToolPak and click on the OK button to install it.
2. Learn the syntax of YEARFRAC. It is YEARFRAC(start_date, end_date[, basis]) where start_date is the first date and end_date is the last date of the desired date range. Basis is an optional argument that specifies the method to use for counting the days.
3. Enter start_date and end_date using the DATE function or some other function that returns a date to prevent the problems that can occur when a date is entered as text. YEARFRAC will return the #VALUE! error value if any of its arguments is not a valid date.
4. Specify basis, if needed. The default is zero and indicates the days in the month are to be counted with the United States' 30-day method and a 360-day year. A 1 uses the actual number of days in the year. A 2 uses the number of days in the month with a 360-day year while a 3 assumes a 365-day year. A 4 is the same as 1 but uses the European 30-day method.
5. Study an example of YEARFRAC. =YEARFRAC(DATE(2007,1,1),DATE(2007,7,30),2) returns 0.5833333333. There are 210 days separating these two dates and with a 360-day year, we have a year fraction of 210/360 = 7/12 or 0.5833333333.

Blogger news