Microsoft Office Tutorials and References
In Depth Information
Using YEARFRAC, DATEDIF, or DAYS to Calculate Elapsed Time
YEARFRAC ,, DATEDIF
DATEDIF , or
, or DAYS
DAYS to Calculate Elapsed Time
to Calculate Elapsed Time
If you work in a human resources department, you might be concerned with
years of service in order to calculate a certain benefit. Excel provides
one function, YEARFRAC, that can calculate decimal years of service in
five different ways. An old function, DATEDIF, has been hanging around since
Lotus 1-2-3; it can calculate the difference between two dates in complete
years, months, or days. Excel 2013 adds the DAYS function, which can cal-
culate elapsed days even if one or both of the values are text dates.
The YEARFRAC function calculates the fraction of the year represented by
the number of whole days between two dates (start_dateand end_date). You
use the YEARFRAC worksheet function to identify the proportion of a whole
year ’ s benefits or obligations to assign to a specific term.
This function takes the following arguments:
start_date — This is a date that represents the start date. Dates may
be entered as text strings within quotation marks (for example, "1/30/
1998", "1998/01/30"), as serial numbers (for example, 35825, which
represents January 30, 1998, if you ’ re using the 1900 date system), or
as results of other formulas or functions (for example,
end_date — This is a date that represents the end date.
basis — This is the type of day count basis to use. Figure 11.44 com-
pares the five types of basisavailable:
• If basisis 0 or omitted, Excel uses a 30/360 plan, modified for Amer-
ican use. In this plan, the employee earns 1/360 of a year ’ s credit
on most days. The employee earns no service on the day after any
31st of the month. In a leap year, the employee earns 2/360 of a year
for showing up on March 1. In a non-leap year, the employee earns
3/360 of a year for showing up on March 1.
• If basisis 1, the actual number of elapsed days is divided by the
actual number of days in the year. This method works well and en-
sures that the year fraction ends up being 1 on the anniversary
date, whether it is a leap year.