Excel has a little-know function called NETWORKDAYS that is available
in Office 2000. Typed in a spreadsheet, it would be =NETWORKDAYS
In order to use it, you'll need to install the Analysis Toolpak. You
do this by going to Tools/Add-ins, then make sure that the Analysis
Toolpak is selected. If you have trouble, you might try putting your
Office or Excel CD in the computer, then going down the following menu
structure to get the tookpak installed:
Excel/Add-ins/Analysis Toolpak/Update Now
Here's how this great little project management tool works:
The format is:
=NETWORKDAYS("startdate", "enddate", "holidays")
where the following are used:
* dates are in the YYYY/MM/DD format
* cells and ranges of cells can be used but without the quotation
marks
Here's an example, with the entire spreadsheet line, including the "="
sign to indicate that it's a calculation:
=NETWORKDAYS("2002,1/1", "2002/2/2", "2002/1/23")
The result to the above line in Excel yields 23 -- the number of
workdays minus the Martin Luther King holiday.
If you want to put all of your dates in cells, here's the command
format:
=NETWORKDAYS(C3, C4, D3:D4)
In this case, I've put the project start date in C3; the project end
date in C4; and a list of holidays in the D column. Note that there
are no quotation marks around cells -- though Microsoft documentation
doesn't make it clear.
The command calculates workdays by eliminating weekend days. But
since holidays can change from state to state or country-to-country,
Microsoft excludes any listing of holidays. You could make a column
of holidays for the year, then simply enter D3:D10 in your range.
I've created a very rudimentary example in Excel at this address:
http://www.lucidmatrix.com/uploads/Networkdays.xls
Don't hesitate to ask for any clarifications: Microsoft instructions
are generally good but sometimes leave holes. And installing add-ins
sometimes doesn't go smoothly either.
Best regards,
Omnivorous-GA |
Clarification of Answer by
omnivorous-ga
on
10 Sep 2002 14:23 PDT
The corollary to NETWORKDAYS in adding work days to a project is
WORKDAY,
which has the format:
=WORKDAY("startdate",days,holidays)
The same types of format issues (regarding cells and deleting
quotation marks) apply here. Excel will return a serial number date
(from 1/1/1900) -- but you can get a day-of-the month back with the
following structure:
=DAY(WORKDAY("2002/1/1", 20, D3:D4))
|
Request for Answer Clarification by
prpro-ga
on
10 Sep 2002 15:49 PDT
For backwards planning, how would I set it up for an end date
scenario? The =WORKDAY("startdate",days,holidays) formula in your
first clarification doesn't work here. Sorry to be dense... ;-)
|
Clarification of Answer by
omnivorous-ga
on
10 Sep 2002 17:33 PDT
PRPro -- it's not at all a matter of being dense. Though I've long
worked with Excel, I had more than a few hiccups in getting all of
this to work. Exact format is important.
I suspect that your problem is that Excel is returned the serial date
(37,286 in the example that I posted on-line). This is the number of
days since January 1, 1900 -- which is where Excel starts by default.
Believe it or not, Excel doesn't have a way to convert serial date
back to a real YYYY/MM/DD. For project use, the DAY command converts
it to a day of the month -- so please note that in project management
your instruction would be:
=DAY(WORKDAY("startdate",days,holidays))
Why would they only give the day-of-the month? The assumption is that
you're running a series of short project timelines. Also, note that
if you put the command above in one column, you can have the column
before it with this command:
=MONTH(WORKDAY("startdate",days,holidays))
* Special care needs be taken to get the parentheses in the right
place.
Questions for you:
1. were you able to see the example that I posted on-line?
2. can you let me know what you're getting with your entries in a
spreadsheet? It's not a difficult matter for me to re-run numbers for
you -- please just keep the example short.
3. do you have a site where you can post a file that I can look at?
BTW, I could have used your help the other day on a Google Answers
question. Someone was asking for the names of the top 50 fashion
editors. My kingdom for a Bacon's Guide!!
This isn't the best format for trying to decipher what's wrong in a
spreadsheet, but I'll do the best I can to make it work for you!
Best regards,
Omnivorous-GA
|
Request for Answer Clarification by
prpro-ga
on
11 Sep 2002 00:22 PDT
Actually, I did do your scenario -- it was perfect -- and it works
very well for FORWARD PLANNING when I have a start date, know the
number of days required for each step of the process, but have no
specific dates calculated for each step and the final end date.
However, my problem is also being able to create a BACKWARDS PLANNER,
when I know the END DATE, know the number of days required for each
step of the process, but have no specific dates assigned for the steps
and START date.
Here's an example:
Begin pitching ? (START DATE)
Information to calendar editors ?
Conduct pre-announcement briefings 9/3/2002
Announcement date 9/1/2002
I don't have anyplace I can post the spreadsheet; I can e-mail, if
you'd like, but don't know if that is allowed. ;-) Thanks for all
your help so far!
|
Request for Answer Clarification by
prpro-ga
on
11 Sep 2002 00:24 PDT
PLEASE IGNORE MY LAST POSTING; I SCREWED UP! HERE IS THE CORRECT MESSAGE:
Actually, I did do your scenario -- it was perfect -- and it works
very well for FORWARD PLANNING when I have a start date, know the
number of days required for each step of the process, but have no
specific dates calculated for each step and the final end date.
However, my problem is also being able to create a BACKWARDS PLANNER,
when I know the END DATE, know the number of days required for each
step of the process, but have no specific dates assigned for the steps
and START date.
Here's an example:
Begin pitching ? (START DATE)
Information to calendar editors ? (Takes 1 day from previous step to do)
Conduct pre-announcement briefings ? (Takes 14 days from previous step to do)
Announcement date 9/1/2002 (END DATE)
I don't have anyplace I can post the spreadsheet; I can e-mail, if
you'd like, but don't know if that is allowed. ;-) Thanks for all
your help so far!
|
Clarification of Answer by
omnivorous-ga
on
11 Sep 2002 09:27 PDT
Working backwards from a fixed date is actually pretty easy, as Excel
numbers each day between now and the end of year 9999 serially. So
you'll be taking the end-of-the project on 9/1/2002 and subtracting
(working days + holidays). All that remains is translating everything
back into English with the DAY and MONTH commands that were mentioned
previously.
I've done this in a simple example for you here:
http://www.lucidmatrix.com/uploads/Backwardplanning.xls
To do all of this you'll do the following:
1. Use the WORKDAY command to calculate days. You go backwards by
subtracting days!
2. To get Excel to recognize that you want to subtract days, highlight
the cells showing # of days for each project step; then designate
negative numbers with a minus sign or by using parentheses.
3. Make a minor tweak to your spreadsheet by setting up a cell that
starts with the known: the product launch date.
4. Don't forget to include your holiday range. I've expanded it to
include all common U.S. holidays -- but you can add Texas Day if
you're in the Lone Star state or other local holidays.
I've put some of the above notes on the spreadsheet as a reminder!
Note that the entering the end-date is a first step. While there's
nothing to prevent you from including this with forward planning, it
would probably be simpler to keep a separate file: it will be clearer
what the purpose is; others using your planning tool won't be as prone
to misinterpret things; etc.
Good luck! But we've already missed the product launch date ;=)
Best regards,
Omnivorous-GA
|
