So you would like to be able to quickly examine your project schedule to see which tasks your team should be working on this week…or…maybe you would like to see which tasks you should work on next week. The following is a dynamic approach, quick to implement, and will meet this need.

1. From within your schedule, click on >View, >More Views, select Gantt Chart, and click on Copy.

2. For Name, type “Date Range View”

3. For Filter, select Date Range…

4. Check Highlight Filter and click on OK. Click on Close to close the More Views dialogue box.

5. Click on >View and select the new >Date Range View

6. A dialogue box will appear asking for you to input the beginning date. Note that this will “Show tasks that start or finish after” the selected date. Always select the first day of the week even if it falls on a Sunday. Click on OK.

7. Another dialogue box will appear asking for you to input the ending date. Note that this will show tasks that start or finish “before” the selected date. Always select the last day of the week even if it falls on a Saturday. Click on OK.

8. Now all of the tasks that are scheduled to be worked on within the selected date range will be automatically highlighted.

1. Open the project in MS Project

2. Click on >File, >Save As. Choose the location where you would like this file to be saved.

3. Click on the dropdown for Save as type and select Microsoft Excel Workbook (*.xls)

4. Click on Save

5. The Project Export Wizard will open, click on Next

6. Choose Selected Data and click on Next

7. Choose New Map. Click on Next.

8. Select Tasks and ensure Export includes headers. Click on Next.

9. The Task Mapping dialogue box will appear. Click on the dropdown in the first row under “From: Microsoft Office Project field” and select ID.

10. Click on the dropdown in the second row and input Name.

11. Continue adding the following fields:

ID
Name
Start
Finish
% Complete
Resource Names

12. Click on Next

13. Click on Save Map (for future reuse)

14. Name this map, then click on Save.

15. Click on Finish

16. Let’s say I am the Project Manager for this project and I would like to get updates from my team on their progress. I could attach this Excel file to an email and send it to the team members.

17. Let’s say that the date is 11/07/08 and Angie Krause (a team member) would like to update the progress on her tasks.

18. Open the file in Excel. Click on >Data, >Filter

19. Click on the dropdown for the Resource Names column, uncheck “Select All” and check Angie Krause. Click on OK.

20. Now Angie sees only the tasks assigned to her.

21. The next step is to update the Start date, the Finish date and the % Complete of these tasks. The objective is to bring this information in line with reality.

22. The first three tasks started and completed as expected, so Angie input 100% on those tasks.

23. The fourth task (Task ID 10: Identify budget requirements) is only 50% Complete and instead of completing on 11/07/08, Angie updates the Finish date to be Tuesday 11/11/08.

24. Angie then saves and closes this file and sends it back to the project manager.

25. To incorporate Angies updates back into the project, we must first open the project in MS Project.

26. Within MS Project, click on Open and browse to where Angies updated Microsoft Excel file has been saved. You will need to click on the dropdown in the bottom right to see “All files”.

27. Select Angies updated file in Excel and click on Open. The Import Wizard will again open. Click on Next.

28. Select Use Existing Map and click on Next.

29. Select the map that we saved earlier (Export to Excel) in step 14 and click on Next.

30. Select Merge data into active project and click on Next.

31. Ensure Task and Import includes headers is checked. Click on Next.

32. All fields should already be mapped correctly.

33. Click on ID and then click on Set Merge Key

34. Click on Next, then click on Finish

35. If we drill into the details of this project file and insert the % Complete field, we can see Angies updates have been applied.

36. By examining the Tracking Gantt view, we can see the impact of Angies updates to our baseline.

*A few Cautions:

1. This approach forces Microsoft Project to impose a Finish No Earlier than constraint on any task that is not 100% Complete. As a work-around, the project manager could modify the Excel file prior to distributing so only current period tasks are included.

2. Because we are exporting Task level information, this approach only works with one resource assigned to each task. If you have multiple resources assigned to tasks, you would want to work with the Assignments table on the Export map instead of the Task table.

3. You may want to password protect all columns within the Excel document except Start, Finish and % Complete to keep these fields from being altered.

4. Do NOT add new tasks to your project until after importing the updates. Remember that alignment between the Excel and the MS Project file data is established by the ID column. Inserting a new task will create a new ID thus causing your files to be out of alignment.

1. Create a view that contains the desired data

2. Select the data you would like to copy within Microsoft Project

3. Click on >Edit, >Copy Task (Ctrl-C)

4. Within Excel, click on Paste

Note that the selected information is pasted accurately, but it lacks the column headings, the WBS or outline structure and it does not retain the bold formatting for Summary Tasks or the Project Summary Task. Also note that the date format now includes the time.

This approach is quick and simple but makes it very tough to decipher the details of this project. Additional work or formatting may be required to get this file in a workable state.

How to Create Multi Color Summary Bar Based Upon Phase

The following steps will show you how to create this:

Note that color formatting is dynamic and will adjust as Duration of the phase increases or decreases.

1.  Must establish consistent Phase names. Example:

- Planning
- Execution
- Closure

2.  Must establish consistent Milestone names. Also useful to have Acronym for milestone names. Example:

Milestone - Acronym
- Planning Complete – PC
- Execution Complete – EC
- Closure Project - CP

3.  Open MS Project

4.  Establish Phase Names and Milestone Names:

5.  Select the Milestones, right click and click on Task Information.

6.  Place a check in “Roll up to Gantt bar to Summary”

7.  Click on OK. Rolled up Milestones will now appear on Summary Bars

8.  Right click on the Gantt Chart and click on Bar Styles

9.  Click on *Rolled Up Milestone, then click on the Text tab. For Bottom, select Name.

10.  Click on OK. Milestone Names will now appear below the Rolled up Milestones on the Summary Bars

11.  Insert Flag 1, Flag 2, and Flag 3 columns

12.  Right click on Flag 1 and click on Customize Fields

13.  Click on Rename and input “Planning Flag”

14.  Click on OK

15.  Click on Formula

16.  Input the following formula: [Name]=“Planning”

17.  Click on OK

18.  For “Calculation for task and group summary rows”, ensure “Use Formula” is enabled.

19.  Click on OK

20.  “Yes” should now automatically appear under the Planning Flag for the Planning Summary Task:

21.  Right click on Flag 2 and click on Customize Fields

22.  Click on Rename and input “Execution Flag”

23.  Click on OK.

24.  Click on Formula

25.  Input the following formula: [Name]=“Execution”

26.  Click on OK

27.  For “Calculation for task and group summary rows”, ensure “Use Formula” is enabled.

28.  Click on OK

29.  Right click on Flag 3 and click on Customize Fields

30.  Click on Rename and input “Closure Flag”

31.  Click on OK.

32.  Click on Formula

33.  Input the following formula: [Name]=“Closure”

34.  Click on OK

35.  For “Calculation for task and group summary rows”, ensure “Use Formula” is enabled.

36.  Click on OK

37.  You may now hide the three flag fields

38.  Click on Show Outline Level 1

39.  Right click on the Gantt chart area and click on Bar Styles

40.  Under Name, click on Summary

41.  Click on Cut Row

42.  Click on Paste Row four times

43.  Move to the second Summary under Name and input “Rolled Up Summary Planning”

44.  Move to the Show For column, input a coma after Summary, click on the dropdown, select Rolled up. Input a coma after Rolled up, click on the dropdown and select Flag 1. Then click on Rolled up Summary Planning

45.  On the Bars tab for Start, Middle and End, click on the dropdown and select the color you would like to appear for the Planning Phase.

46.  Complete steps 43 through 45 for the 3rd and 4th Rolled Up Summary

47.  Click on OK. Colors will now appear on Summary row

48.  To add the Phase Names below each phase, right click on the Gantt Chart and click on Bar Styles

49.  Click on Rolled Up Summary Planning under Name

50.  Click on the Text Tab

51.  For Bottom, Select Name

52.  Click on OK

53.  Complete steps 49 through 52 for Rolled Up Summary Execution and Rolled Up Summary Closure

54.  Click on OK

There are a number of factors that dictate how Microsoft Project “thinks”.

The two main factors are:

The Scheduling Formula leverages three variables:

Many people say that the scheduling formula is: Units x Duration = Work

When you initially assign a Resource to a Task, this is the formula that is leveraged. Let’s demonstrate:

We will create Task 1 that is 5 days in Duration

We will now assign a Resource to this task

As soon as we assign this Resource to this task, Work changes to 40 hours.

Here is the how that number was calculated:

Units x Duration = Work
100% x (5*8) = 40
Or
100% x 40 = 40 hours

But using simple math, we can re-write our equation to solve for a different variable.

Our original formula solves for Work:
Units x Duration = Work

But we could re-write the formula to solve for Units:
Work / Duration = Units

Or we could re-write the formula to solve for Duration:
Work / Units = Duration

So we have shown that The Scheduling Formula can actually be written three different ways:

Units x Duration = Work
Work / Duration = Units
Work / Units = Duration

Now let’s prove that Microsoft Project also thinks this way.

In order to demonstrate this, we must introduce The Task Type or the Type field:

By default, Type is set to Fixed Units. But there are actually three different Task Type variables:

Now to test Microsoft Project

Using our original scenario, let’s force Microsoft Project to solve for Duration.

We will leave Type set to Fixed Units and change Work to 80 hrs

Here is how Duration was calculated:

Work / Units = Duration
80 / 100% = 80
Or
80 / 100% = (80 / 8)
Or
80 / 100% = 10 days

Returning to our original scenario, let’s force Microsoft Project to solve for Work.

Leave Type set to Fixed Units and enter 10 days Duration

Here is how Work was calculated:

Units x Duration = Work
100% * 10 days = 80
Or
100% * 10 days = (10 * 8)
Or
100% * 10 days = 80 hours Work

Returning to our original scenario, let’s force Microsoft Project to solve for Units

Change Type to Fixed Duration and enter 20 hours Work

Here is how Units was calculated:

Work / Duration = Units
20 / 5 days = 50%
Or
20 / 5 days = 20 / (8 * 5)
Or
20 / 5 days = 20 / 40
Or
20 / 5 days = 0.5

To Review:

There are three different Task Types:

There are three different variables of the Scheduling Formula:

Depending upon how the Task Type is set in combination with which Scheduling Formula variable is altered dictates which variable Microsoft Project solves for.

The Task Type and Scheduling Formula Cheat Sheet will help you control how Microsoft Project “thinks”:

See The Microsoft Project Task Type and Scheduling Formula Cheat Sheet here.

See this article in Slide format here.

See this article in Video format here.

Purchase “The Cheat Sheet” Mouse Pad here.

Definitions:

Committed - A resource that is formally allocated to any task assignments they have within a project. This is the default booking type.

Proposed - A resource with a pending resource allocation to a task assignment that has not yet been authorized. This resource assignment does not detract from the availability of the resource to work on other projects.

1.  Within your schedule, click on >View, >Resource Sheet

2.  Insert the column Booking Type

3.  Code your resources either Committed or Proposed

4.  Develop your schedule assigning resources to tasks

5.  Click on >View, >Resource Usage

6.  Click on >Window, >Split

7.  Click anywhere on the bottom pane to activate

8.  Click on >View, >Resource Graph

The Resource Graph will now appear in the bottom pane. Note that this shows a graph for Peak Units. (this is not the graph we would like to leverage)

9.  Right click in the TimePhased portion of the Resource Graph and click on Work

10.  Right click in the TimePhased portion of the Resource Graph again and click on Bar Styles. The following dialogue box will appear:

11.  Under Selected resources, for Overallocated work (left side) show as Bar. For Allocated work, show as Bar. For Proposed Bookings, Show as Bar.

12.  Under Resource, for Overallocated work, Show as Don’t Show. For Allocated work, show as Don’t Show. For Proposed Bookings, Show as, Don’t Show. As a final step, check the Show Availability line. Here is how the dialogue box should be configured:

Click on OK!

13.  Now, click on the first resource name and lets examine the screen.

14.  Now click on the second resource name, and let’s inspect this view.

15.  Now click in the blank square in the upper left corner (above the ID number) to select all resources.

16.  Before leaving this exercise, let’s look at one more scenario. Let’s play with a Role.

17.  On the Resource Sheet, set Resource 2 Booking Type to Committed. Then add a resource named Role 1. Set the Booking Type to Proposed….AND…the Max Units to 0%.

18.  Now let’s say two new tasks are identified for our project. We are not sure exactly which resource we want to assign to those tasks yet, so we assign Role 1.

19.  Moving back to our combination view of Resource Usage on top and Resource Graph on bottom, let’s examine the details.

By clicking on Role 1, we see there is 40 hours of Proposed Work for the weeks of July 6th and July 13th. But notice there is no Availability line. This is because Role 1 is actually a placeholder and has no 0 hours of availability. We need to figure out which resource we can assign these tasks to.

20.  Now hold down the Control Key and click on Resource 1.

From this graph we see that if we moved this work from Role 1 to Resource 1, Resource 1 would become overallocated. We would be asking Resource 1 to do 80 hours worth of Work for the weeks of July 6th and July 13th.

21.  Let’s see if Resource 2 could handle the Work. Click on Role 1, hold down your Control Key and click on Resource 2.

Upon examining this graph, we can see that Resource 2 has the availability to handle this Proposed Work.

EXTRA: How to easily transfer the assignments from Role 1 to Resource 2.

1.  Click on the row to highlight Task 5.

2.  Hover over this row within the indicator column. You will see your cursor turn into 4 arrows. Now hold down with your left mouse button and drag this task until it is under Task 4. Release the left mouse button.

3.  Click on the row to highlight Task 6. Hover over this row within the indicator column. You will see your cursor turn into 4 arrows. Now hold down the left mouse button and drag this task until it is under Task 5. Release the left mouse button.

4.  Click on Resource 2

Notice that Role 1 no longer has any assignments. Resource 2 has all 4 assignments and the graph indicates all this work can be done without causing Resource 2 to be overallocated.

Creating a Checklist or To-Do List in Microsoft Project

  Exam
Name Microsoft Office Project 2007, Managing Projects
Microsoft Office Project Server 2007, Managing Projects Microsoft Office Project Server 2007, Configuring Microsoft Office Project Server 2007, Managing Projects and Programs Microsoft Office Project Portfolio Server 2007, Managing Portfolios* Exam
Number

70-632 70-633 70-639 70-634 70-635* Tool Focus Microsoft Project 2007



Microsoft Portfolio Server 2007 Credential Microsoft Certified Technology Specialist (MCTS)

Microsoft Certified Technology Specialist (MCTS)
Microsoft Certified Technology Specialist (MCTS) Microsoft Certified IT Professional (MCITP) TBD* Prerequisite N/A N/A N/A Microsoft Certified Technology Specialist (MCTS)
TBD* Date Available

July 2007 October 2007 January 2008 January 2008 4th Quarter 2008 Preparation Guides How to Prepare
for the Microsoft Project 2007 Certification Exam

Preparation Guide for Exam 70-633 Preparation Guide for Exam 70-639 Preparation Guide
for Exam 70-634 TBD* Exam Prep Questions Free Microsoft Project Certification Exam Prep Questions

        Exam Prep Books Managing Projects with Microsoft® Office Project 2007

Managing Projects with Microsoft® Office Project Server 2007       Self Paced Training Products

Other Microsoft Project Books Other Project
Server Books Other Project Server Books Other Project
Server Books   Exam Prep Courses

Course 5927 Course 5928
Course 5929




  Process Focus

Project Management Project
Management Server and Network Administration Project and Program Management Portfolio Management Additional References
Microsoft Project Microsoft Project Server Microsoft Project Server Microsoft Project Server Microsoft Portfolio Server







Number of Microsoft EPM Certifications worldwide



Social Bookmark this page

RSS Feed for Exclusive Articles