Microsoft Office Tutorials and References

In Depth Information

**Using YEARFRAC, DATEDIF, or DAYS to Calculate Elapsed Time**

Using

Using
YEARFRAC

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.

Syntax

=YEARFRAC(start_date,end_date,basis)

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

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,

DATEVALUE("1/30/1998")).

•
end_date

end_date
—
This is a date that represents the end date.

•
basis

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.