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

Flexibility and the ability to be self-directed are key skills in today’s environment. Organizations are reorganizing and downsizing, requiring individuals to multi-task, contribute to multiple projects, and assume many responsibilities, often in very different roles. Depending on the organizational structure, individuals may need vastly different skills for their various tasks.

The roles of the project manager and project team members differ. Yet the same person may, at times, be the project manager of one team and a member of a different team. Or, a proj­ect manager may suddenly find her project has been cancelled, positions are being eliminated, and job retention means taking a job as a member of another team. That requires changing hats very quickly, and the results can be detrimental if you wear the wrong hat to the wrong meeting.

Two hats required
In this article, we’ll provide guidelines for doing a graceful balancing act between the roles of project manager and team member, using Michelle as our example. We’ll see how she balances between her roles as project manager and team member. First, we’ll look at the responsibilities of each position. Next, we’ll see how Michelle handles her project manager role and interacts with the team members during a team meeting. Then, we’ll watch her behavior at a product meeting in which she’s a team member. We’ll identify where she had problems changing her hat and the problems that ensue. Finally, we’ll discuss how Michelle could behave differently to promote a far more positive outcome.

Understand Michelle’s responsibilities
Michelle is a mid-level manager at a mid-sized company that develops software products for the optics industry. She acts as project manager for all training or documentation projects required to support the software her company develops. Additionally, she must participate as a member on the product team for each new software product.

As project manager for her teams, Michelle is responsible for commu­nicating new projects and assigning resources. She’s in charge of approving the media used for the projects, moni­toring those projects, removing any barriers to success, and reviewing the quality of the projects. Additionally, she’s responsible for maintaining a program of skill development for her teams and guiding her members toward professional growth.

Michelle’s role on the product teams is very closely tied to her teams’ functions. However, she isn’t the man­ager of these teams, but simply a team member representing a functional area. She’s responsible for understand­ing the new product, its development timeframe, and the requirements for her functional area. Working with other cross-functional team members, she develops an overall training and docu­mentation plan for the product. She’s responsible for working with the engi­neering representative to identify the subject matter experts who will work with her team. She provides engineer­ing with an estimate of when and how long subject matter experts are needed. She coordinates schedules with the development team to secure access to products for her trainers and writers.

Participate differently in meetings
Due to different personality styles, project managers may vary in how they approach their roles during team meetings. Yet, regardless of the stylistic differences, team members expect their project managers to lead and demonstrate their control. They expect other team members to defer to this control.

Do’s for project managers
Here are some do’s for the project manager during meetings:

• Do manage the agenda. The agenda may include introducing the new product the team will be working on and describing the team’s involvement with the new product. The agenda will include administration items, such as scheduled shutdowns, changes in office procedures, and more.
  
• Do convey information from management and from the teams on which you’re a member.
  
• Do assign resources to your projects. Consider the competencies, interests, and availability of team members when assigning resources to the project.
  
• Do keep the meeting focused on business. It’s up to the project manager to rein in those who stray from the topic at hand and to get input from those who may be reluctant to participate.
  

Michelle, the project manager in our example, conducts weekly meetings with her teams. When one team member comes in late and asks Michelle what was already covered, she tells him to see her after the meeting so she can bring him up to speed.

Dont's for project team members
Just as team members expect certain behaviors from the project manager conducting a meeting, they have expectations for other team members. These include the following:

• Don’t take charge of the meeting.
  
• Don’t usurp the role of the project manager. Some team members may like to be in control and may be capable leaders, yet respectful team members allow the designated leader to lead. But team members can take a participatory role.
  
• Don’t treat team members as subordinates.

Let’s look in on Michelle at a product team meeting. Here, she announces she’d like to discuss some items from the last meeting. The project manager asks her what those items are so he can cover them at the end of the meeting. Rather than simply stating the items as asked, Michelle digresses and questions the development representative about system availability. The project manager finally directs the team back to the agenda by scheduling a follow-up meeting with Michelle and the development representative.

When the engineering represen­tative walks in late and asks what he missed, Michelle tells him to meet with the project manager after the meet­ing. Later in the meeting, the project manager requests that representatives adjust their plans to accommodate new product features. Michelle suggests including the other team members when sending the revised plans, so everyone can review them before the next meeting. As the project manager reaches the end of the agenda, Michelle reminds everyone to review the revised plans before the next meeting.

Select the right hat
Clearly, Michelle had a difficult time transitioning from her project manager role to her team member role. As a project manager, she was in charge, running her meeting and setting the rules and agenda. Switching to her team member role, Michelle continued to take charge. She clearly wore the wrong hat to that meeting.

First, she begins the meeting, which isn’t her meeting, by saying what she wants to discuss. Then, she pulls the development representative into a specific discussion that’s outside the scope of the agenda. This would probably make the project manager either angry or frustrated and cause him to lose focus, get defensive, or otherwise abandon control of the situ­ation. It could also make other team members uncomfortable.

When the engineering representa­tive arrives late, it’s Michelle who tells him to catch up later with the project manager. When the project manager isn’t leading the meeting, other team members can become annoyed, less focused, or uncomfortable. Michelle steps into the project manager’s role one more time when she reminds team members to send their plans to the proj­ect manager before the next meeting.

Get used to the team member role
Michelle appears to have a good grasp of her role as project manager. She acknowledges her team members’ needs, asks for their input, takes control of her meetings, and follows her agenda. She’s polite, yet in control.

How could she have better han­dled her role as a team member? She could have waited for the meeting to begin and asked to add her items to the agenda. This would have allowed the project manager to establish his position as team leader and keep con­trol of the agenda.

It wasn’t Michelle’s place to decide how to handle the engineer’s late arrival. She could have let the proj­ect manager handle it. If the project manager chose to delay the rest of the team by reviewing the earlier part of the meeting for the engineer, Michelle could have chatted with the project manager after the meeting or sent him an email suggesting how she’s handled those sorts of things successfully. Much of her behavior went beyond the scope of her team member role and clearly detracted from the project manager’s authority. Michelle has a hard time taking off her project manager hat. Here are some tips that might help a project manager make the transition to team member:

• When you’re put in the position of filling both roles, write up a role definition, much as you would when you’re starting a project and identifying the necessary roles and responsibilities. This will help reinforce the differences in the roles.
  
• Recognize which role is more natural for you and be aware of your tendency to take on that role. You may ask team members to keep an eye on you and let you know if you overstep your role. The fact that you’ve recognized the possibility and asked for help will gain you respect, even if you do slip now and then.
  
• Listen and watch the people around you. If team members bristle, roll their eyes each time you speak, interrupt you, or ignore you, evaluate your behavior.

Successful balancing benefits everyone
The transition from project manager to team member can be a tricky one for some people. Don’t feel you need to completely bury your experience in one role or the other. A team member with project manager experience can help tremendously. Giving the project manager an occasional second opinion or a bit of advice on how to handle a problematic situation is often welcome support. Conveying to the team the challenges the project manager faces can help the entire team work more cohesively.

Related Courses
• Project 2000, 2002, 2003 & 2007
• 4009 Setting Up Projects for Success Using Microsoft Office Project 2003
• Project Management for Professionals
• Project Management Fundamentals