Combining 2 duration columns to get one Finish date

[MSprojecthelpme]

I am looking to combine 2 "Duration" columns.  One will be the contractual duration and the second column will be a delay column i.e. material, weather, man power etc.  I don't need a lead time or lag time.  I am looking to be able to "add" up these 2 columns and have it so my finish date adjusts using both columns.

Thanks for any help!

[#2]

MSprojecthelpme --

There is no way to do what you seek using a formula. You would need to use VBA to accomplish this. My colleague, John, is a VBA expert. I suspect he will be able to assist you with this. Hope this helps.

[#3]

@MSprojecthelpme 

Bear in mind that duration in Project doesn't accomplish anything (except for paint drying, concrete curing, wounds healing, etc.), it is simply the span of time during which a task is performed. Work is what gets things done. I think you should be concentrating on the planned work versus actual and remaining work and Project has fields for each of those metrics.

Are you familiar with Project's baseline functionality? When a plan is developed and ready for execution, saving that plan in Baseline fields provides a comparison point for how the plan actually unfolds. It sounds like you want to compare the original plan (i.e. "contract duration") with the current schedule (i.e. reality) and the Baseline Variance field provides that, if you save a baseline before starting.

The finish date is determined by the current schedule (i.e. changes in the plan as a result of how things unfold during plan execution).

Hope this helps.

I just noticed Dale suggested I could do what you want with VBA and indeed I could but I believe you're going down the wrong path.

John

[#4]

I do use the baseline function and add new baselines at major milestones but I am wanting to be able to see the "contractual duration" and then look at the column beside it and see how many working days I have added to a task due to weather or material delays.

Lets say roofing started and we are 2 days in out of 4 days total and it happens to rain for 2 days. Instead of changing my overall duration from 4 to 6, thereby "deleting" my contractual date from the schedule, I would like to add a "Delays" column and add 2 days to that. Then I would like to make it where the finish date is able to add those 2 columns together ( 4 Day contractual and 2 day weather delay) to come up with my start and finish dates.

Thanks

[#5]

I am unfamiliar with VBA. Can you send me some info?

Thanks

[#6]

MSprojecthelpme,
Please explain more about this "contractual duration". I've never heard of such a thing, contract requirements for critical dates or finish date but never duration. Are you telling us each and every task has a contractual time span requirement?

If you saved a baseline you will not be "deleting" anything from the schedule. As I explained before, the whole point of a baseline is to provide a comparison of the original (contract?) schedule with the current schedule. So your "delays" column is the Duration Variance field.

Even if I wrote a VBA macro to do the manipulation you want the end result would be no different than simply updating the current schedule by increasing the schedule duration.

What am I missing?
John

[#7]

@John-project 

Sorry if my explanations aren't clear. 

So as you can see in the picture below roofing is set to be 3 days.  If it rains for 2 days, while they are in the middle of roofing, instead of increasing it from 3 to 5 days duration, I want to add the weather delays into "Duration1".  When I do this I am looking at some way to add both of those Duration columns together so that it will automatically change my Planned Finish from 12/17/21 to 12/21/21(2 days later).

So basically add "Duration" (Contractual Duration) and "Duration1" (Delays Duration) to get my planned finish.

I want to easily be able to see the amount of time they should have taken and the amount of days delays has added.  However with Duration Variance I can only see the baseline difference and not adjust Duration Variance.

Does that clear it up at all?

Thanks,

.

[#8]

MSprojecthelpme,
No, not really, but let's play along. After reading your explanation a few times it seems you want to have a separate custom field where you can manually enter delays and then have that delay modify the plan, a kind of "after the fact" scheduling approach. Okay, let's try it.

In your roofing example it rained for 2 days so you enter that value in Duration1 and that ups the task duration to 5 days. What happens if on day 4 you discover the supplier is short 10 bundles of shingles and it will take a day to get them shipped in from another warehouse. Will you then enter that delay into another Duration field or will you change the entered value in the Duration1 field so it now shows 3 days (original 2 for rain delay and new 1 day delay for shingles)?

You say you want to easily see the amount of delay and that's exactly what the Duration Variance field supplies. But for some reason you want to take the extra step to separately enter the delay into a custom field which is then used to update the task Duration when you could just as easily enter a new task duration directly into the task Duration field, saving the extra field and step. The delay visibility is still available via Duration Variance.

For reference, VBA stands for Visual Basic for Applications and it is an integral part of most Microsoft applications. It allows a user the ability to create application functionality that is not available "out of the box"

John

[#9]

John, I think we are on the same page now. And my Duration1 column would just be all delays for that specific task in general, whether it is weather or material, and I guess another way to put it is this:

I more or less want to track the delay for each individual task item. Say I have a project that is 11 different buildings. Over the span of a year the framing delays could be say 60 working days by the time we get to building 11. The framers have 20 days to frame a building. The duration variance will show they are 60 days behind according to the original baseline. However, they may get the building done in 18 days which puts them ahead of schedule on that specific task item.

So I guess I'm saying all that to say that yes I would like to do an after the fact adjustment so I can track delays on every specific task that is delayed. To put it in excel terms I want to be able to add Column A(Duration) and Column B(Duration1) to equal my finish date. I am not very well versed in MS Project but is there not a custom fields formula that would allow me to do this?

Thanks

[#10]

MSprojecthelpme,
I'm not sure we're on the same page but at least I know the book you are reading. I'm just reading a different book.

Let me answer your last question first and then let's talk about your example. Excel is an open format, any cell can interact with any other cell via formulas. Project is a strictly defined format with several base fields, of which Duration is one, that cannot be changed with a formula (other than through VBA). The only fields in Project that can be customized with a formula are the extra fields (e.g. Duration1, Duration2, Text1, Text2, etc.) and the customization can only apply to data on the same task/resource line..

Now with regard to your framing example. You say you want to track all delays for each specific task. How exactly then does that translate to the total 60 days delay for framing what I assume is all 11 buildings? And if on a specific building the time span allotted for framing is 20 days but the framers do it in 18, then how does the -2 days of Duration Variance factor into the 60 days? Was the total framing delay for all buildings actually 62 days but because the framers "beat the clock" by two days, they are "credited" with those two days so the overall total framing delay is 60 days?

Perhaps a simple example showing your intent via screen shot would help.

I can easily write a VBA macro to do what I think you want but there are more things to consider than what you have presented so I need more specifics.

John

[#11]

@John-project 

OK on the project above we will say framing has been going on for 5 days, then they hit a 10 day working delay.  What I want to show is that framing, since they have started their task, is now 10 days past they're original duration after they had started.  So instead of finishing on 6-28-22 they will now be finishing on 7-12-22(12 days+10 days).  This in turn will bump back all successors back 10 days as well.  However, since "Doors and Windows" haven't started yet this task technically doesn't have any weather and/or material delays so they should still be able to complete their task in 2 days still (+ 10 day adjustment to start date due to framing delay.)

So my ultimate goal is to only track individual task items that are going to be affected by delays, as they happen, not track the overall project delays like the Duration Variance does.

[#12]

MSprojecthelpme --

Pardon me for bumping into this post. Why don't you create a custom task Duration column named Total Delays and then enter this information for individual tasks that have experienced delays? You can add one or more notes to tasks that are delayed and explain the causes for the delays. Just a thought. Hope this helps.

[#13]

MSprojecthelpme,
Keep in mind that what you want to do is probably all very clear in your mind but we can't read your mind so you have to think like you are explaining it to a third party because that's what we are.

Your latest example of the 10 day delay on a started framing task seems to play with your latest stated goal of tracking individual tasks that are delayed. But what happened to your previous example of the 60 day delay on framing? And what about the framing where the framers finished two days early? You only want to track the bad things and not track good things?

I don't understand your comment about tracking delays "as they happen" and not track overall project delays like duration variance. The Duration Variance field tracks delays as they happen. When a user becomes aware of a delay and needs to change a task duration, as soon as the user enters a new Duration, the Duration Variance field is updated. Are you saying your intended manual entry of a delay into the Duration1 field is more of a "as it happens"?

Just curious, what are you going to do with the "delay" metric (i.e.. the big picture)?

John

[#14]

@John-project 

My overall goal is to go to each individual trade (framer) and show them the schedule and show them how many days they are behind on their contractual days for each of their individual task items.  If I use the Duration Variance to do that it will show them the amount of days the entire project is behind from the baseline that got saved at the beginning of the project.  Which lets say is 60 days by the time the framers get to the last building in the sequence.  If they haven't started that last building how could they already be 60 days behind on their work?  So I need to show a running total of delays for each task individually and not an overall project delay(Like the Duration Variance does).  And yes as you stated the variance does track delays as they happen but then those delays affect every successor even if they haven't started their task yet.

Again if framers end up pushing back the entire building schedule due to weather the Duration Variance will automatically show a delay for MEP rough ins/roofers/siders/painters.  My goal is to show individual task delays as the delays happen.  For another example, if the framers frame the building without a hitch but then the plumbers have a delay but the HVAC, Electrical, and Sprinkler guys don't, I want to be able to add 2 days to the plumbers duration in my "Delays" column for only them.  Thus, allowing me to go to the plumbers and say hey you're 2 days behind I need you to pick it up.

For our projects we only save a baseline when an initial schedule has been agreed upon, and then when framing starts on the first building.  If my framers get to the last building and it shows a 60 day delay from the "Framing Baseline" mentioned above, I cant say "Here is your back charge for being behind by 60 days."  I have to hold them accountable for each building individually.  When they start framing a new building that is when the clock starts on their duration.

[#15]

I am here for any and all advice/solutions so I appreciate your comment. And that is actually mostly what I am trying to do with a small caveat. If I add that "Total Delays" column and then put a number in it doesn't change my "Planned Finish" column. This means I would still have to go back to the "Duration" column and add the delays to that column as well. What I am trying to do is get the "Duration" column and "Total Delays" column to interact with each other and add up those days to get a new Planned Finish date. Then I would go into the notes column and say 2 days for weather, 3 days for material, etc.

[#16]

@John-project 

Sorry this is the picture i meant to upload, if it clears anything up....

[#17]

@MSprojecthelpme 

Sorry, there is no picture or screen shot. Did you use the little camera icon to upload a picture? If not, you can also include a link to an readily accessible page that has your picture.

John

[#18]

@MSprojecthelpme,

It may seem like Dale and I are giving you a hard time about your approach and in a way perhaps we are. Our suggestions are based on a whole lot of years of using Project, (20+ for each of us), and fielding a array of questions and misunderstanding by users. Back in 2020 I helped a user, also in the construction industry, who asked for help with translating the dollars associated with their cost codes for each type of construction activity. We went round and round trying to get on common ground but eventually I wrote a set of procedures (VBA macros) for him that interfaced from Excel to Project and back to Excel, one of which was a customized Excel cash flow report for all their properties under construction.

Back to the topic at hand. I don't quite see where you 60 day delay is coming from unless your plan shows all the framing tasks as a single task. Here is an example of 3 buildings with framing followed by rough in plumbing. A baseline is set.

It's raining so the framers are delayed 5 days on building 1 so that 5 days is added to the duration and entered into the Duration field. Note the framing delay shows in the Duration Variance for framing but even though that shifted the overall schedule to the right, rough in plumbing has no delay.

On to building 2, the framers pick up some time so they finish 2 days early but the plumbers run into a material delay so they lose a day. Again, no framing delay on building 2, (actually they get an "attaboy" for finishing early), but there is a plumbing delay on building 2.

Now, what part of that doesn't work for you?

John

[#19]

@John-project 

Ok so I was confused on Duration Variance(dont use that function very often.), I thought if you had a 5 day delay on framing and plumbing it would add those 2 durations together.  Then it would report that plumbing was starting 10 days later than it should have.  

So I guess I want a Duration Variance that translates into the finish date of a task, without adding days to my Duration column.  Instead I would be adding days to a "Delay" tab.  Then both of those columns combined would push the finish date back for that task.

[#20 · Accepted solution]

@MSprojecthelpme 

Well, it will report that plumbing was starting 10 days later but that's Start Variance, not Duration Variance.

Sorry but you can't add something to the finish date without increasing the Duration field, unless you also change the Start field. Duration is the difference in working days between the start of a task and the finish of a task, unless there is a split, then task duration only tracks the scheduled working days where the span of the split is excluded. For example, in the screen shot below, framing for building one was going along swimmingly for the first three days, then over the weekend a storm blew in and delayed further framing for 3 days. That 3 days is entered into a custom Duration field (Duration1 renamed as "Delays") and a split is applied to the framing task. Note the task Duration field still shows the original 20 days but the split delay shifted the schedule to the right by those 3 long rainy wet days (yuck). If other delays occur, additional splits can be applied. The advantage here is the the delay is graphically very apparent but you could, as Dale suggested, make an entry in the Task Notes field why the split occurred.

So how do you do a split? It can be done manually via Task > Schedule group > Split task icon and then hovering your mouse over the start of the split and pulling it over the 3 days. Or, it could be done programmatically with VBA, enter the delay, run the macro and boom! there it is.

Is you head spinning yet?

John