Microsoft Office Tutorials and References

In Depth Information

**Using Other Functions**

NETWORKDAYS

The NETWORKDAYS function calculates the

number of workdays between two dates. By

workdays, I mean Monday–Friday, minus any

obvious holidays such as Christmas and New

Year’s. The syntax is:

Understanding the Time

Arguments

The Year argument should be a four-digit

one, so Excel knows for sure what century

you’re talking about (1911 vs. 2011). The

Month and Day arguments can be negative.

If Month is negative, Excel subtracts that

many months, plus one, from the year

speciﬁed to determine what month you mean. For

example, if you enter =DATE(2001,–2,13),

then the result is 10/13/00 (three months

from the beginning of 2001). If Day is

negative, Excel subtracts that many days,

plus one, from the beginning of the month

speciﬁed. For example, =DATE(2010,8,–15)

results in 7/16/10 (16 days from August 1).

=NETWORKDAYS(StartDate,EndDate,Holidays)

The last argument, Holidays, is optional, but it

allows you a way to specify the holidays your

office uses, such as Martin Luther King Day,

Veterans Day, and so on. The argument is a range,

such as M2:M12, that contains the dates you want

treated as holidays. So for example, if you had a

worksheet that listed the starting and ending

date of a job, you could calculate the number

of workdays it took to complete that job with a

formula like this: =NETWORKDAYS(C2,D2).

Another example is to compute a date so many

years, months, or days from some other date.

For example, suppose an employee review is due

three months from an employee’s hire date. You

could add 180 (3*30) to the hire date, but that’s

not exactly accurate, as some months have 31

days and one of them has 28 or 29 depending

on the year. No, all you want to do is to take

a date like 2/15/10 and add three months to

get 5/15/10. So you use the DATE function

and a few old friends to add three months to

the hire date, which let’s say is stored in cell E4:

=DATE(YEAR(E4),MONTH(E4)+3,DAY(E4)).

Obviously, this formula, with a small tweak,

would work easily in situations where you need

to add or subtract so many years from a date,

like this example, which adds 25 years to a date:

=DATE(YEAR(E4)+25,MONTH(E4),DAY(E4)). To

add or subtract days, you can be more

straightforward: =E4+15.

DATE

The DATE function allows you to compose a

date, using each of its three arguments: Year,

Month, and Day. The syntax is:

=DATE(Year,Month,Day)

The DATE function is useful in situations where

the month, day, and year of a date are stored in

separate cells, as they might be in a large

database. For example, if you had a membership

database for your parents’ association that listed

everyone’s birth month in one column and birth

day in another, you could use the DATE function

to compose their actual birthday this year,

assuming the birth months are in column G

and the birth days are in column I:

=DATE(YEAR(TODAY(),G2,I2).