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.
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.
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:
- Never never never touch the start date (except the first task).
- Never never never touch the finish date
- Manage the schedule by adjusting duration and dependency.
- 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.