IT Computer Training and Certification - Atlanta, Columbus, Memphis, Nashville, Omaha, Pittsburgh

Planning a project is often easier said than done. If your staff is only in the office on weekdays, you’ll need to omit weekends when building your project schedule. There are many complex project management programs on the market that can make this task less daunting, but they don’t come cheap. Fortunately, Excel offers the only tools you’ll need — the WORKDAY and NETWORKDAYS functions.

Automate your project planning with a few simple functions

Install the necessary ToolPak
These functions are included in Excel’s Analysis ToolPak add-in. If the Analysis ToolPak isn’t enabled, the functions will return a #NAME? error.

To install the Analysis ToolPak:

1. Launch Excel and open a new workbook.
2. Choose Tools Add-Ins from the menu bar.
3. In the Add-Ins dialog box, select the Analysis ToolPak check box in the Add-Ins Available list box, and then click OK.

WORKDAY function syntax

The WORKDAY function uses the following syntax:

WORKDAY(start_date,days,holidays)

The start_date argument is the date to which you want to add workdays, and the days argument is the number of workdays you want to add. The holidays argument is optional; if there are any holidays you want to exclude from your workday calculation, just specify their dates as the holidays argument.

Create the sample worksheet
Let’s see how these functions can simplify your project management.

To set up the worksheet:

1. Enter the necessary text and formatting.
2. Apply Date number formatting to cells D3:E11 with the Format Cells dialog box, but leave those cells empty for now.

Calculate the due date
The value in the Due Date column should calculate the due date by adding the number of workdays specified in the Allotted Days column to the date specified in the Start Date column. With the WORKDAY function, this task will be easy.

To calculate the due date based on allotted days:

1. Enter 9/15/08 in cell D3 and press the [Tab] key to activate cell E3.
2. Type =WORKDAY(D3,C3) in cell E3 and press [Enter].

Analyze the formula’s results
The due date in cell E3 displays as 10/03/08. If you check your calendar, you’ll find that there are 14 workdays between 09/15/08 and 10/03/08. Notice that the start date, 09/15/08, is included as one of the 14 workdays; however, the due date, 10/03/08, is not. The WORKDAY function begins counting each day at 12:00 a.m. So, the six workdays our sample formula calculates fall between 12:00 a.m. 09/15/08 and 12:00 a.m. 10/03/08. Simply put, if you start the First Draft stage first thing in the morning on 09/15/08 and have 14 days to work on it, it will be due first thing in the morning on 10/03/08.

Fill in the remaining start dates
Let’s set up each remaining stage’s start date to match the preceding stage’s due date. By using a cell reference, the start dates update automatically if there are any changes to the preceding due dates.

To synchronize your start dates and due dates:

1. Select cell D4 and type =E3, or type an equals sign (=) and select cell E3.
2. Press [Ctrl][Enter] to save the formula without changing the active cell.
3. Double-click on the cell’s Fill handle to copy the formula down to the remaining Start Date cells.

To copy your WORKDAY formula to other cells:

1. Select cell E3.
2. Double-click on the cell’s Fill handle down to copy down the formula.

Since your WORKDAY formula contains relative references, the copied formulas update when you paste them in a new location.

Tip: In our example, you can not only plan each stage of a project before you’ve started it, but you can also revise your project schedule along the way. For instance, imagine that the Production Schedule stage took only four days as opposed to the five days you originally assigned. If you change the value from 4 to 5, all of the dates affected update automatically.

Exclude holidays from your calculation
At this point,our sample worksheet plots start dates and due dates that begin on 09/15/08 and end on 02/13/09. However, there are a lot of holidays that take place during that time period, including Thanksgiving and Christmas. Let’s account for the 11/20/08 and 12/25/08 holidays in each relevant project stage.

To add company holidays to your worksheet:

1. Select cell A13 and type Company Holidays.
2. Press [Enter], type Thanksgiving, and then press the [Tab] key.
3. Enter 11/20/08, press [Enter], and then type Christmas in cell A15.
4. Press [Tab] again and enter 12/25/08.
5. Select cells A13:B15, click on the Borders button’s dropdown list borders in the Formatting toolbar, and then choose Thick Box Border from the resulting palette.
6. Select cells B14:B15 and apply the same Date number format you applied to cells D3:E11.

Note: If you don’t want to put your holidays or other exceptions right in your worksheet, you can input the holiday argument as a date within quotes. For example, instead of =WORKDAY(D3,C3,B14), you can enter =WORKDAY(D3,C3,"11/20/08"

To exclude holidays from a time span:

1. Select cell E7 and click in the formula bar to place your insertion point directly before the closing parenthesis.
2. Type ,B14 in the formula bar and press [Enter] to accept the change.
3. Select cell E8 and click in the formula bar to place your insertion point directly before the closing parenthesis.
4. Enter a comma and select cell B15.
5. Press [Enter] to accept the holiday.

Note: If Excel tags a cell in which you’ve added a holiday to the WORKDAY
function with a green triangle in the upper-left corner, don’t be alarmed. Excel is letting you know that the formulas you AutoFilled in the Due Dates column are now inconsistent. If you click on the smart tag and choose Ignore Error from the
resulting shortcut menu, the tag disappears.

You can include as many holidays as you like, as long as you also include them in your holidays argument. In our example, we included two vacation days in cells A18:A19 in addition to the company holidays.

Notice that each time you add a holiday as a date for Excel to exclude from the workday count, the other dates in the worksheet update accordingly.

Note: If you’re entering your holiday argument with text (e.g., "10/12/08"), you can include more than one by putting the dates in curly braces ({}) and separating them with a comma. For instance, you can enter the formula =WORKDAY(D11,C11,{"8/22/08","8/23/08"}).

Count the workdays that fall between two dates
What if you know which dates you want to start and end your project, and you’d like to find out how many workdays you have to accomplish it? This is where the WORKDAY function’s relative, the NETWORKDAYS function, can work its magic.

Change the scenario
With the end and start dates already decided, you just determine the values in the Allotted Days column. The only difference in syntax between the WORKDAY function and the NETWORKDAYS function is that the NETWORKDAYS function’s second argument is end_date instead of days.

To find the number of allotted days:

1. Select cells D3:E11 and press [Ctrl]C to copy the values.
2. With these cells still selected, choose Edit Paste Special from the menu bar to open the Paste Special dialog box.
3. Select the Values option button in the Paste panel and click OK. Excel converts the date values from formula results to static values.
4. Select cells C3:C11 and press [Delete] to clear the values.
5. Select cell C3 and enter =NETWORKDAYS(D3,E3).
6. Press [Ctrl][Enter] to accept the formula without changing the selected cell.
7. Double-click on the cell’s Fill handle to copy down the formula.

You can use the same procedures for excluding holidays and vacation days from the allotted days in the NETWORKDAYS function as you did with the WORKDAY function.

Related Courses

• Project Management Fundamentals
• Excel 2003 - Level 2
• Excel 2003 - Level 3
• Excel 2007 - Level 2
• Excel 2007 - Level 3

Adapt for Excel 2007
To install the Analysis ToolPak in Excel 2007, you need to click the Office button and then click the Excel Options button. Select Add-Ins in the left panel. Choose Excel Add-ins from the Manage dropdown list and click Go. Then, you’ll see the familiar Add-Ins dialog box where you can install the add-in as you would in earlier versions.

The WORKDAY and NETWORKDAYS functions operate the same in Excel 2007 as they do in earlier versions. And in 2007, a helpful ToolTip including the function’s arguments pops up as you type your formula in the Formula bar.

Business skills for the new world of work
In business today, productivity is key to your success. Whether that means setting up projects for success, forecasting and analyzing trends, or managing critical business information, it is vital that you have the skills to work at peak performance. You already know how to use Microsoft® Office System applications. New Horizons offers Microsoft Business Skills Series Courses to teach you how to use those applications to more efficiently manage, work with, and prioritize information to make better decisions. Go to www.NewHorizons.com for information on courses that cover topics such as:

• 4004 Managing Critical Business Information Using Microsoft Office Access 2003
• 4007 Creating Effective Presentations Using Microsoft Office PowerPoint 2003
• 4008 Building Better Microsoft Office Word 2003 Documents In Less Time