Keep Task Days Together

[MSPuser]

Hi,

I have a project that uses a “Spring & Fall Only†calendar with winter and
summer designated as none-working time. The challenge is to keep tasks from
being auto-scheduled to start in spring and finished in the fall.

For example, a 5-day task with the predecessor which finishes on 05/29/06
can be scheduled by MS Project from 05/30/2006 to 09/05/2006. This is not
acceptable, as the task must be completed within 5 calendar days, i.e. it
must be moved to 09/04/06 – 09/08/06.

Putting constraints would not be a good solution as the schedule has
hundreds of dependent tasks and it may require frequent adjustments back and
forward in the future.

Please suggest a way to auto-move the task forward to assure that it would
be always completed within 5 days no matter when the predecessors finishes.

Thank you in advance.

 

[John]

Hi,

I have a project that uses a “Spring & Fall Only†calendar with winter and
summer designated as none-working time. The challenge is to keep tasks from
being auto-scheduled to start in spring and finished in the fall.

For example, a 5-day task with the predecessor which finishes on 05/29/06
can be scheduled by MS Project from 05/30/2006 to 09/05/2006. This is not
acceptable, as the task must be completed within 5 calendar days, i.e. it
must be moved to 09/04/06 – 09/08/06.

Putting constraints would not be a good solution as the schedule has
hundreds of dependent tasks and it may require frequent adjustments back and
forward in the future.

Please suggest a way to auto-move the task forward to assure that it would
be always completed within 5 days no matter when the predecessors finishes.

Thank you in advance.

MSPuser,
You have a very unique scheduling need. Project just isn't set up to
easily shift tasks as in your example but it is possible if a simple VBA
macro is used to automatically adjust the schedule. The macro could be
set up to run on user demand or it could be set up to run automatically
when the file is opened or closed.

However just a comment. It appears to me that a simple shift of task
duration won't reflect reality. If there is that much delay between
working periods, I would think some "reorientation overhead" would be
needed. For example, let's say a task is 5 days duration and would
ordinarily start on May 28. Because of the end of May cutoff, the task
would need to be re-scheduled to the first of September. However, my
guess is that whoever is going to do the task will NOT be able to just
pick it up and start working on Sept. 1. Very likely there will be some
prep work before the task can actually be started. I could be wrong, but
there aren't too many things that can just be shelved for long periods
of time and then be instantly re-started.

John
Project MVP

 

[MSPuser]

John,

Thank you for your reply and pointing me in the direction of using a macro.

Here is a clarification to some of your points:
<<You have a very unique scheduling need.>> - Actually it is pretty common
in electric industry where many tasks can only be performed in spring or fall
when the electric demand is lower.

<<whoever is going to do the task will NOT be able to just
pick it up and start working on Sept. 1. Very likely there will be some
prep work>> - Yes, it will be. In our case the prep work will include some
construction that will be done by a different crew and can be done at any
time before the electric crew does their work in spring or fall. In fact it
is a possible delay in construction in May that could move it successor, the
electric work, forward to September 1.

<< it is possible if a simple VBA macro is used to automatically adjust the
schedule.>> - Thank you. How would it work? For example, add a lag of 3
months and several days to assure that the electric work starts on September
1 if MS Project initially scheduled it to start in May and end in September?
Please note that we have a multiyear project with hundreds of tasks. Then
next time there are some schedule changes Project would need to delete all
previously added lags, recalculate the new ones and add them back (?) Please
advise.

Unfortunately I have never tried writing a VBA macro in MS Project. I would
appreciate a reference to a good source.

Thank you again.

MSPuser

 

[John]

John,

Thank you for your reply and pointing me in the direction of using a macro.

Here is a clarification to some of your points:
<<You have a very unique scheduling need.>> - Actually it is pretty common
in electric industry where many tasks can only be performed in spring or fall
when the electric demand is lower.

<<whoever is going to do the task will NOT be able to just
pick it up and start working on Sept. 1. Very likely there will be some
prep work>> - Yes, it will be. In our case the prep work will include some
construction that will be done by a different crew and can be done at any
time before the electric crew does their work in spring or fall. In fact it
is a possible delay in construction in May that could move it successor, the
electric work, forward to September 1.

<< it is possible if a simple VBA macro is used to automatically adjust the
schedule.>> - Thank you. How would it work? For example, add a lag of 3
months and several days to assure that the electric work starts on September
1 if MS Project initially scheduled it to start in May and end in September?
Please note that we have a multiyear project with hundreds of tasks. Then
next time there are some schedule changes Project would need to delete all
previously added lags, recalculate the new ones and add them back (?) Please
advise.

Unfortunately I have never tried writing a VBA macro in MS Project. I would
appreciate a reference to a good source.

Thank you again.

MSPuser

MSPuser,
I understand your special circumstances but I'm still not buying in to
the idea that a task can be picked up and started immediately after a
delay of several months - but then that's just my observation and not
really relevant to your question.

With regard to the VBA approach. I would NOT used a lag. In my opinion
any lag of greater than 5 days is worthless. A much better approach is
to simply set a "start-no-earlier-than" constraint. I would still keep
the link to insure the logic is correct but only if the task indeed has
a predecessor that must be completed first. The following code will do
what you need, assuming there is no setup time for a delayed task.

Sub Schedule_Gapper()
Dim Cutoff As Date, Restart As Date
Dim t As Object
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Cutoff = "5/30/" & Year(t.Start)
Restart = "9/1/" & Year(t.Start)
If t.Summary = False And t.Start < Restart Then
If Application.DateAdd(t.Start, t.Duration) > _
Cutoff Then t.Start = Restart
End If
End If
Next t
End Sub

If you want to learn more about Project VBA, go to our MVP website at:
http://www.mvps.org/project/links.htm
and look for the link at the bottom of the page, "Project 98 Visual
Basic Environment Training Materials". Even though it says it is for
Project 98, it is equally applicable to all current versions of Project.

Hope this helps.
John
Project MVP

 

[MSPuser]

John,

Thank you very much again for pointing me in the right direction and for
providing valuable code.
I tried it and had to modify it for a real schedule for the following reasons:

1. It would move ALL the tasks, even those that use the Standard calendar,
as long as they start before the Restart date and have Start+Duration >
Cutoff date. To avoid that I modyfied the IF statement to check for
t.Calendar = "SpringAndFall".

2. The code you wrote puts the “Start Not Earlier Than†constraints in
order to move the tasks. Next time the schedule is adjusted backward (if
construction finished earlier), the tasks would not move back because of the
constraints. To avoid that I put a loop that removes all previously placed
constraints before re-applying them.

Please see the code below. I would appreciate your comments.
--------------------------------------------
Sub Schedule_Gapper2()
Dim Cutoff As Date, Restart As Date
Dim t As Object

'First remove all previously created constraints
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Restart = "9/1/" & Year(t.Start)
If t.Summary = False And t.Calendar = "SpringAndFall" Then
If t.ConstraintDate = Restart And t.ConstraintType = pjSNET
Then
t.ConstraintType = pjASAP
End If
End If
End If
Next t

‘Reapply Constraints
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Cutoff = "5/31/" & Year(t.Start)
Restart = "9/1/" & Year(t.Start)

‘Removed as it moves regular tasks also
'If t.Summary = False And t.Start < Restart Then
'If Application.DateAdd(t.Start, t.Duration) > _
'Cutoff Then t.Start = Restart
'End If

If t.Summary = False And t.Calendar = "SpringAndFall" Then
If t.Start <= Cutoff And t.Finish >= Restart Then
t.Start = Restart
End If
End If
End If
Next t
End Sub

 

[John]

John,

Thank you very much again for pointing me in the right direction and for
providing valuable code.
I tried it and had to modify it for a real schedule for the following
reasons:

1. It would move ALL the tasks, even those that use the Standard calendar,
as long as they start before the Restart date and have Start+Duration >
Cutoff date. To avoid that I modyfied the IF statement to check for
t.Calendar = "SpringAndFall".

2. The code you wrote puts the “Start Not Earlier Than†constraints in
order to move the tasks. Next time the schedule is adjusted backward (if
construction finished earlier), the tasks would not move back because of the
constraints. To avoid that I put a loop that removes all previously placed
constraints before re-applying them.

Please see the code below. I would appreciate your comments.
--------------------------------------------
Sub Schedule_Gapper2()
Dim Cutoff As Date, Restart As Date
Dim t As Object

'First remove all previously created constraints
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Restart = "9/1/" & Year(t.Start)
If t.Summary = False And t.Calendar = "SpringAndFall" Then
If t.ConstraintDate = Restart And t.ConstraintType = pjSNET
Then
t.ConstraintType = pjASAP
End If
End If
End If
Next t

‘Reapply Constraints
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Cutoff = "5/31/" & Year(t.Start)
Restart = "9/1/" & Year(t.Start)

‘Removed as it moves regular tasks also
'If t.Summary = False And t.Start < Restart Then
'If Application.DateAdd(t.Start, t.Duration) > _
'Cutoff Then t.Start = Restart
'End If

If t.Summary = False And t.Calendar = "SpringAndFall" Then
If t.Start <= Cutoff And t.Finish >= Restart Then
t.Start = Restart
End If
End If
End If
Next t
End Sub

MSPuser,
My simple macro was based on the limited information you provided and
there certainly is nothing wrong with modifying the code to meet your
needs. I'm glad to see you "picked up the ball and ran with it".

Some comments on your modified code. First, the two loops could be
combined - no need to go through the tasks twice. As long as calculation
is set to automatic, the task will "snap" back based on its predecessor
as soon as the constraint is lifted. The next line of code can set a new
start date if appropriate. Second, the variable "Restart" is defined
twice - it only needs to be defined once as long as the value is not
being changed. But then this becomes mute if the two loops are combined.
Third, the first loop assumes that all tasks with a
"start-no-earlier-than" constraint were set by a previous run of the
macro - that may not always be the case (i.e. some tasks in a project
may not have a valid predecessor and therefore will have their own
independent start date, and most likely, a SNET constraint to go with
it). You might want to set a flag field to separate the two types of
tasks - flag is true if the SNET constraint was set as a result of
running the macro.

Hope this helps.
John
Project MVP