Microsoft Office Tutorials and References

In Depth Information

**Working with specialized date functions**

falls exactly 23 months after June 12, 2014, type the formula
=EDATE("6/12/2014", 23)
,

which returns the value
42502
, or May 12, 2016.

The EOMONTH function returns a date that is an indicated number of months before or

after a given date. Although EOMONTH is similar to EDATE and takes the same arguments,

the value returned is always rounded up to the last day of the month. For example, to

calculate the serial date value that is the last day of the month 23 months after June 12, 2014,

type the formula
=EOMONTH("6/12/2014", 23)
, which returns
42521
, or May 31, 2016.

Using the YEARFRAC function

The YEARFRAC function calculates a decimal number that represents the portion of a year

that falls between two given dates. This function takes the arguments (
start_date
,
end_date
,

basis
), where
start_date
and
end_date
specify the period of time you want to convert to a

fractional year. The
basis
argument is the type of day count you want to use, as described in

Table 15-3.

For example, to determine what fraction of a year is represented from 4/12/10 to 12/15/10,

you can type the formula
=YEARFRAC("4/12/14", "12/15/14")
. This formula returns

0.675
based on the default 30-day month and 360-day year.

TABLE 15-3
Basis codes

If
basis
is
YEARFRAC returns

0 (or omitted)

30/360, or 30 days per month and 360 days per year, as established in the

United States by the National Association of Security Dealers (NASD)

Actual/actual, or the actual number of days in the month(s)/actual days in

the year

1

Actual/360

2

Actual/365

3

European 30/360

4

Using the WORKDAY, NETWORKDAYS, WORKDAY.INTL, and

NETWORKDAYS.INTL functions

The WORKDAY and NETWORKDAYS functions are invaluable for anyone who calculates

payroll and benefits or determines work schedules. Both functions return values based on

working days, excluding weekend days. In addition, you can choose whether to include

holidays and specify the exact dates. The .INTL forms of these two functions include an

additional argument you can use to specify exactly what constitutes a weekend.

The WORKDAY function returns the date that is an indicated number of working days

before or after a given date. This function takes the arguments (
start_date
,
days
,
holidays
),

where
start_date
is the date you want the function to count from and
days
is the number