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
specified 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
specified. 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).
 
Search JabSto ::




Custom Search