1

I am building a project management template (from the standard excel template) that tracks the status of activities as they progress. Some activities are dependent on previous ones so they can only start once another is complete. The time between the activities are also varied. How can I build this into the functionality? Right now I can just set the activities to start on specific days and have a specific duration but are not interdependent.

Attempts at increasing functionality

A bit cleaned up

  • I've used software packages like Merlin to create Gantt charts that auto-adjust. This should be a feature of most Project Management software systems, but they tend to be somewhat expensive (I think Merlin was $99 when I bought it). Either way, something to look into. – recursions Sep 11 '16 at 16:01
  • I like what you did. It looks really good. Yet for me, excel can be used only as a visualization tool when the subject is a gantt chart. Resorce non working time, complicated formulas and broken formulas can turn excel-gantt maintenence into a nightmare. too much can go wrong and being the one that put the progect off track is not an option for a pmo – Asaf Oct 21 '16 at 8:03
1

Ironically (given my arrogant answer above), I found myself in the same situation; new job hasn't provisioned project for me, so I'm forced to develop a schedule in excel.

In column A, I have a WBS number (floating point)

Column B is the deliverable (text)

Column C is the duration (integer, days)

Column D is the dependency - floating point - this contains the WBS number of the deliverable that must be complete before this activity begins.

Column E is the start date - "=lookup(D2,A:A,F:F) - this grabs the dependency number and looks it up in the table, then returns the finish date. The start date is the finish date of the predecessor.

Column F is the finish date - calculated as the start date plus the duration "=workdays(E7,c7)"

Obviously this fails if task X depends on tasks A and B; it can only manage single dependencies. I have to use milestones to capture those.

Hope that helps a bit.

0

This is core to the function of a gantt chart; gantt charts adjust schedule based on dependency and delay.

Couple of rules I had to learn the hard way:

  1. Never never never touch the start date (except the first task).
  2. Never never never touch the finish date
  3. Manage the schedule by adjusting duration and dependency.
  4. Use driving milestones heavily

Sensei project solutions had a very good intro to pragmatic WBS/Gantt; I can't find it right now, but it may be worth your while to write to them.

I just spotted the fact that the question addresses excel, not project. My apologies for missing that.

The bulk of my advice remains the same; manage duration and dependencies and allow code to manage start and finish dates. Make sure you have a cleanly written formula to calculate the date offset (Task X finish date plus duration of task Y is the finish date for Y) and then spend 80% of your time writing an ironclad dependency function (the start date for Task X is the last actual finish date for task A, B, C....) Given that you don't know how many dependencies a given task will have, this is going to be fiendish to develop in excel.

The only modification I would make is to run away fast now. Excel isn't as good as it thinks at dates; there are too many chances for error and I predict that you'll spend an inordinate amount of time managing exceptions and error checking code. I'm not going to say it can't be done, but I believe that if you get it working, you'll have spent time you could h

If you make the assumption that every deliverable has one and only one predecessor (which is not realistic), then you might be able to make this work by setting the start date for every deliverable to be the finish date of the precursor plus the variance of the precursor, and the finish date for every deliverable to be the start date plus the duration. if tasks are late, the variance will delay the start of all subsequent tasks. If tasks are early, negative variance will speed the start of subsequent tasks.

protected by Tiago Cardoso Mar 21 '17 at 9:02

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.