Terug naar de startpagina

Representing Non-Working Time as Tasks in Microsoft Project

By Jan De Messemaeker, Microsoft Project Most Valuable Professional

The author

The author runs Prom+ade BVBA . He is a consultant on Project Management, specifically on the role of Microsoft Project in these methods. The article below is a response to a question Jan has been hearing with nearly all his customers, and it offers a complete solution to the dilemma.

Introduction

A frequently asked question about “best practices” in Microsoft Project usage is about the use of unmovable tasks to represent non productive activities rather than their introduction into the resource calendar as non working days. Whereas the latter seems the most natural, and certainly the simplest as non-working days are immediately taken into account by the calculation - it has a few drawbacks:

Load reports over time such as shown by the Usage views will show the resource as underallocated. This may raise questions about how well the project manager uses the resources he’s got, and will force him at every review to explain this is holiday or any leave of absence, not underallocation;
When two or more resources are assigned to a task, and the requirement is for these resources to work simultaneously, entering a non-working day for any of the resources will break the simultaneity. For instance, when for a job you need a crane and a crane driver, and the driver takes a day off, Microsoft Project will schedule the crane to “do its part of the work” during the driver’s holiday.

Using immovable tasks to represent non-working days will solve both these problems, as is explained below. Moreover, it will provide a visual “Gantt Chart” representation of holidays. However, many project managers have already been introducing non-working time in the resources’ calendar. This means that migrating to this alternative representation can imply quite some work, which as such has only little added value for the project manager himself.

The VBA macro presented here will perform this migration automatically, creating these immovable tasks tasks and eliminating non-working days from existing calendars.

Scheduling without non-working days in the calendar

Obviously, representing non-working time as tasks will solve the reporting problem as a fully allocated resource will show a 100% occupation in any Usage report when his absences are counted as worked time. However, with “normal” scheduling the method will cause two distortions in the plan: the cost of the non-working time will be allocated to the project, and work on “real” tasks happily goes on during these absences.

The cost problem can be avoided in two ways. Either you create for each resource a cost rate table with zero cost, and assign this table to each non-working assignment or (which is the solution presented in the macro attached) you put the non-working tasks in a separate project such that they do not influence the cost of your original project.

As for the scheduling problem, there is Resource Leveling. This highly underestimated feature of Microsoft Project, solves our problem as:

it will schedule the “real” tasks around the non-working periods and
it has the option to force assignments on a task to be simultaneous (see how the options fro solving overallocations have to be set in the figure joint).

Converting an existing file through a macro

Convinced about the goodies of this method? If your project already exist, and you want to replace calendar non-working time by fixed task (“Must Start On” constrained) you are faced with a tedious task. I want to alleviate this for you by supplying a free macro that does the trick.

Here’s the code:

'=================================================================

'**********************************************************************

Sub Create_NWT_Tasks()
Dim Original_File, BaseName, BackupName, NWTfile As String
Dim Original_Project, BUproject, NWTproject As Project
Dim AnyTask, NWTtask As Task
Dim Worker, BUworker, NWTworker As Resource
Dim ReadDate As String
Dim RunDate, NWTTaskStart As Date
Dim WorkerID As Integer
Dim TaskIsMade As Boolean
Dim NWTduration, MPD As Single
Dim Counter As Integer
Dim tasknbr As Integer
DisplayAlerts = False
TaskIsMade = False
NWTduration = 0
MPD = ActiveProject.HoursPerDay * 60
'----------------------------------------------------------------
'Create 2 additional files from the existing one:
'A copy that will keep track of non-working days in the calendar
'A file that will contain the NWT tasks
'----------------------------------------------------------------
ResourceSharing False
FileSave
Original_File = ActiveProject.FullName
BaseName = Original_File
If LCase(Right(Original_File, 4)) = ".mpp" Then
BaseName = Left(Original_File, (Len(Original_File) - 4))
End If
BackupName = BaseName & "-BU.mpp"
If Not Len(Dir(BackupName)) = 0 Then
VBA.Kill BackupName
End If
FileSaveAs BackupName
Set BUproject = ActiveProject
FileOpen Name:=Original_File, openpool:=pjDoNotOpenPool
NWTfile = BaseName & "-NWT.mpp"
If Not Len(Dir(NWTfile)) = 0 Then
VBA.Kill NWTfile
End If
FileSaveAs NWTfile
Set NWTproject = ActiveProject
tasknbr = NWTproject.Tasks.Count
For Counter = tasknbr To 1 Step -1
Set AnyTask = NWTproject.Tasks(Counter)
If Not AnyTask Is Nothing Then
AnyTask.Delete
End If
Next Counter
FileSave
FileOpen Name:=Original_File, openpool:=pjDoNotOpenPool
Set Original_Project = ActiveProject

For Each Worker In Original_Project.Resources
WorkerID = Worker.ID

Set NWTworker = NWTproject.Resources(WorkerID)

RunDate = Original_Project.ProjectSummaryTask.Start
Set BUworker = BUproject.Resources(WorkerID)
Do While Not RunDate > Original_Project.ProjectFinish
'----------------------------------------------------------------
'Create or extend the NWT task
'----------------------------------------------------------------
If BUproject.Calendar.Period(RunDate).Working And (Not Worker.Calendar.Period(RunDate).Working) Then
NWTworker.Calendar.Period(RunDate).Working = True
NWTworker.Calendar.Period(DateAdd("d", 1, RunDate)).Working = True
If Not TaskIsMade Then
TaskIsMade = True
NWTTaskStart = RunDate
End If
NWTduration = NWTduration + 1
Else
If TaskIsMade Then
Set NWTtask = NWTproject.Tasks.Add(Worker.Name)
NWTtask.Type = pjFixedUnits
NWTtask.Duration = NWTduration * MPD
NWTtask.Assignments.Add ResourceID:=WorkerID
NWTtask.Priority = 1000
NWTtask.ConstraintType = pjMSO
NWTtask.ConstraintDate = NWTTaskStart
TaskIsMade = False
NWTduration = 0
End If 'TaskisMade
End If 'working combination
RunDate = DateAdd("d", 1, RunDate)
Loop
If TaskIsMade Then
Set NWTtask = NWTproject.Tasks.Add(Worker.Name)
NWTtask.Type = pjFixedUnits
NWTtask.Duration = NWTduration * MPD
NWTtask.Assignments.Add ResourceID:=WorkerID
NWTtask.Priority = 1000
NWTtask.ConstraintType = pjMSO
NWTtask.ConstraintDate = NWTTaskStart
End If
Next Worker
End Sub
'=================================================================

'=================================================================

How to use the Macro

To install the macro:

Open MS Project
Close all files
Alt. + F11
Insert
Module
Paste the code above
Close the VB Editor
Open the file to be converted
Tools
Macro
Macros
select “Create_NWT_Tasks”
Run

You will be requested to enter a date. Non Working days before that date will not be converted into NWT tasks.

At the end of the run you will find three open projects:

Your original project, with non-working days handled as explained above;
A project with the same name followed by -BU, which is a copy of the original file as opened;
A project with the same name followed by -NWT which has the NWT tasks corresponding to the non-working days in the resource’s calendar before the conversion.

More Macros

Jack Dahlgren, another Project MVP, has many more interesting Macros on his site:

Jack Dahlgren's Microsoft Project Tools and Tips

More Information

Do not hesitate to mail for more information on this macro, Resource Levelling, or other Microsoft Project related questions!