Gantt chart in excel using processors

Hello, I need to make a gantt chart in Excel that uses processors relationship (graph). I do not know the start date for each task, however processors should calculate if for me. I want to know the end date of this project or the duration of the project knowing the duration for each task.  I attached file with what I started, but I suck with formula for series for tasks that have two processors. Also, if you can help with adding a critical path to it, it will be great.
Example.xlsx
Iryna253Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
The formula gets complex if you have two or more predecessors. To simplify things, you might consider using a user-defined function with a worksheet formula like:
=StartTime(D3,$B$2:$F$27)

The user defined function is stored in a regular module sheet (like a macro), and requires two inputs:
Predecessors is a cell reference to a comma separated list of predecessor tasks. A blank cell, or one containing a hyphen, will be interpreted as a task having no predecessors.
rgTaskTable is a five column list, where the first column contains task numbers, the fourth contains the start time and the fifth contains the duration.
Function StartTime(Predecessors As String, rgTaskTable As Range) As Double
'Returns the earliest start time for a task
'Predecessors is a comma separated list of predecessor tasks
'rgTaskTable is a five column table, with the first column being task number, fourth column being task start time, and fifth column being the task duration
Dim v As Variant, vPredecessor As Variant
Dim Predecessor As Long
Dim dTestStart As Double, dStart As Double
If Predecessors = "" Then
    StartTime = 0
ElseIf Predecessors = "-" Then
    StartTime = 0
Else
    For Each vPredecessor In Split(Predecessors, ",")
        Set v = Nothing
        Predecessor = Val(vPredecessor)
        v = Application.Match(Predecessor, rgTaskTable.Columns(1), 0)
        If Not IsError(v) Then
            dTestStart = Val(rgTaskTable.Cells(v, 4).Value) + Val(rgTaskTable.Cells(v, 5).Value)
            If dTestStart > dStart Then
                dStart = dTestStart
            End If
        End If
    Next
    StartTime = dStart
End If
End Function

Open in new window


If you need to consider resources, then the fifth column must contain the calculated duration for a task (unit time *batch size / number of workers). If your start time and/or duration are located in a different column, then change the 4 and 5 in the following statement:
 dTestStart = Val(rgTaskTable.Cells(v, 4).Value) + Val(rgTaskTable.Cells(v, 5).Value)

Open in new window

Gantt-with-predecessorsQ28706007.xlsm
Iryna253Author Commented:
Hello, thank you very much for your comment and solution. Can you please take a look at the attached file that i added per your recommendation? I think it is not what I got using Gantt chart program (see attached screen shot). Can you please advise what can be wrong?
Gantt-with-predecessorsQ28706007.xlsm
Screen-Shot-2015-08-16-at-3.10.57-PM.png
byundtMechanical EngineerCommented:
Your revised data layout didn't match the assumptions in the code. To accommodate that layout, you would need to change the statements defining v and dStartTime to reflect the new column assignments.

I fixed it as follows:
1. Inserted a "Start time, hrs" column at column E, pushing the other columns to the right.
2. Populated that column with the formula:
=StartTime(D3,$B$2:$G$27)
3. Modified the chart so Series1 pointed to columns B and E, Series2 pointed to columns B and G. Note that this displays the time line in hours.
4. Formatted Series1 so there was no fill and no line
5. Changed the header labels above columns E, G and I so they reflected what the column contained
6. Cleared values in column A
7. Added a copy of Sheet1 with column G and I formulas revised so they displayed days and hours respectively. In so doing, the chart displays a time line in days--which I suspect you may prefer.
8. Changed the statement in the function that defines dTestStart so it refers to the correct columns
9. Added a test for whether the predecessor was numeric. If not, I left the predecessor as a string. This allows you to label steps like 25A, 25B, 25C (should you ever think of doing so).

Function StartTime(Predecessors As String, rgTaskTable As Range) As Double
'Returns the earliest start time for a task
'Predecessors is a comma separated list of predecessor tasks
'rgTaskTable is a six column table, with the first column being task number, fourth column being task start time, and sixth column being the task duration
Dim v As Variant, vPredecessor As Variant
Dim Predecessor As Long
Dim dTestStart As Double, dStart As Double
If Predecessors = "" Then
    StartTime = 0
ElseIf Predecessors = "-" Then
    StartTime = 0
Else
    For Each vPredecessor In Split(Predecessors, ",")
        Set v = Nothing
        If IsNumeric(vPredecessor) Then vPredecessor = Val(vPredecessor)
        v = Application.Match(vPredecessor, rgTaskTable.Columns(1), 0)
        If Not IsError(v) Then
            dTestStart = Val(rgTaskTable.Cells(v, 4).Value) + Val(rgTaskTable.Cells(v, 6).Value)
            If dTestStart > dStart Then
                dStart = dTestStart
            End If
        End If
    Next
    StartTime = dStart
End If
End Function

Open in new window

Gantt-with-predecessorsQ28706007.xlsm
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

byundtMechanical EngineerCommented:
In case you move the columns around again, I revised the function so you pass it each of the three columns that it needs: Task IDs, Start Times, and Durations. You then use this formula to return the starting time (taking into account the predecessors):
=StartingTime(Predecessors, Task IDs, Start Times, Durations)
=StartingTime(D3,$B$2:$B$27,$E$2:$E$27,$G$2:$G$27)

Function StartingTime(Predecessors As String, TaskID As Range, StartTimes As Range, Duration As Range) As Double
'Returns the earliest start time for a task. Returns the same time unit as used in Duration. _
    So if Duration is in hours, then StartingTime returns hours. If Duration is in days, then StartingTime returns days.
'Predecessors is a comma separated list of predecessor tasks
'TaskID, StartingTime and Duration are columns in a table _
    TaskID is a number (or text) that identifies each task _
    StartTimes is the number of hours (or days) after project beginning for each task to begin _
    Duration is the amount of time (either hours or days) that each task needs to be completed
Dim v As Variant, vPredecessor As Variant
Dim Predecessor As Long
Dim dTestStart As Double, dStart As Double
If Predecessors = "" Then
    StartingTime = 0
ElseIf Predecessors = "-" Then
    StartingTime = 0
Else
    For Each vPredecessor In Split(Predecessors, ",")
        Set v = Nothing
        If IsNumeric(vPredecessor) Then vPredecessor = Val(vPredecessor)
        v = Application.Match(vPredecessor, TaskID, 0)
        If Not IsError(v) Then
            dTestStart = Val(StartTimes.Cells(v, 1).Value) + Val(Duration.Cells(v, 1).Value)
            If dTestStart > dStart Then
                dStart = dTestStart
            End If
        End If
    Next
    StartingTime = dStart
End If
End Function

Open in new window

Gantt-with-predecessorsQ28706007.xlsm
byundtMechanical EngineerCommented:
If you want to avoid VBA, the formula for starting time for 0, 1 or 2 predecessors is:
=MAX(IFERROR(INDEX(E$2:E$27,MATCH(--LEFT(D3,FIND(",",D3 & ",")-1),B$2:B$27,0))+INDEX(G$2:G$27,MATCH(--LEFT(D3,FIND(",",D3 & ",")-1),B$2:B$27,0)),0),
IFERROR(INDEX(E$2:E$27,MATCH(--MID(D3,FIND(",",D3 & ",")+1,9),B$2:B$27,0))+INDEX(G$2:G$27,MATCH(--MID(D3,FIND(",",D3 & ",")+1,9),B$2:B$27,0)),0)
)

The formula will be even more complicated if there might be three predecessors.

Even though you could use a formula without VBA, it would be difficult/impossible to debug the formula if it stops working or your layout changes. This is why my initial suggestion was to use a user defined function in VBA.
Iryna253Author Commented:
Thank you very much for your help with this gantt chart. I was wondering if you can explain how to add formula/VBA if I will have 3 predecessors?

Is it possible to add slacks to this chart?

is it a way to identify all possible network paths in the project with it duration? so i can know where is a critical path.

Lastly, in the second file that I attached (sheet4) I tried to show the distribution of each tasks for each resource, however I wish to see the idle time (gap) for each resource for each day as it shown in a screen short I attached (this is from gantt chart program).
Gantt-with-predecessorsQ28706007--2-.xls
Gantt-for-resources.xlsm
Screen-Shot-2015-08-16-at-5.28.00-PM.png
byundtMechanical EngineerCommented:
I was wondering if you can explain how to add formula/VBA if I will have 3 predecessors?
The VBA code that I have presented can handle any number of predecessors. That's one of the advantages of that approach. The formula methods would definitely need to change to allow for 3 predecessors. Since the formula approach is already unmaintainable in my opinion, I shall refrain from suggesting the modifications.

is it a way to identify all possible network paths in the project with it duration? so i can know where is a critical path.
Although it is possible to write VBA code to identify the critical path, Microsoft has a much better tool for that purpose called Project. As an intermediate step, one could identify the schedule determining predecessor for each task. Doing so is a simple code change. The critical path is then found by working backwards from completion, linking from one schedule determining predecessor to another. This part is also easy--provided that you would be content with a yes/no statement for whether a task is on the critical path. Showing the critical path on the chart--not so easy.

Duplicating the basic features of Project (such as critical path, idle time, and lead/lag time after predecessors) goes far beyond the scope of a single question on Experts Exchange. Those features could be implemented in Excel using VBA in a custom solution (i.e. work for hire), but every place I have worked found it more expedient to purchase a copy of Project for each of their project managers.

Is it possible to add slacks to this chart?
I don't know what you mean by "slacks".
byundtMechanical EngineerCommented:
I added the critical path determination by changing the code to:
Function StartingTime(Predecessor As String, TaskIDs As Range, StartTimes As Range, Durations As Range) As Variant
'Returns an array of the earliest start time for a task and the predecessor task that is latest to be completed
'If bPacingTask is False or omitted, funtion returns the task starting time in the same time unit as used in Durations. _
    So if Durations is in hours, then StartingTime returns hours. If Durations is in days, then StartingTime returns days.
'If bPacingTask is True, then function returns the TaskID of the latest predecessor to be completed _
    In case of a tie, the latest to be completed predecessors are returned as a comma separated list
'Predecessor is a string listing predecessor tasks, separated by commas
'TaskIDs, StartTimes and Durations are columns in a table _
    TaskIDs is a number (or text) that identifies each task _
    StartTimes is the number of hours (or days) after project beginning for each task to begin _
    Durations is the amount of time (either hours or days) that each task needs to be completed
Dim v As Variant, vPredecessor As Variant
Dim PacingTask As Variant
Dim dTestStart As Double, dStart As Double
If Predecessor = "" Then
ElseIf Predecessor = "-" Then
Else
    For Each vPredecessor In Split(Predecessor, ",")
        Set v = Nothing
        vPredecessor = Trim(vPredecessor)
        If IsNumeric(vPredecessor) Then vPredecessor = Val(vPredecessor)
        v = Application.Match(vPredecessor, TaskIDs, 0)
        If Not IsError(v) Then
            dTestStart = Val(StartTimes.Cells(v, 1).Value) + Val(Durations.Cells(v, 1).Value)
            If dTestStart > dStart Then
                dStart = dTestStart
                PacingTask = vPredecessor
            ElseIf dTestStart = dStart Then
                PacingTask = PacingTask & ", " & vPredecessor
            End If
        End If
    Next
End If
If dStart = 0 Then PacingTask = ""
If IsNumeric(PacingTask) Then PacingTask = Val(PacingTask)
StartingTime = Array(dStart, PacingTask)
End Function

Open in new window


I added two columns to the worksheet. In one, I put a formula returning the "Pacing predecessor" for each task. These would be the predecessor(s) with the latest ending time:
=INDEX(StartingTime(D3,$B$2:$B$27,$E$2:$E$27,$G$2:$G$27),2)

In the other column, I put a formula that returns "x" if the task is on the critical path, and an empty string (looks like a blank) if it is not:
=IF(ROWS(J$3:J3)=ROWS(J$3:J$27),"x",IF((COUNTIFS(J$3:J$27,B3,K$3:K$27,"x")+COUNTIFS(J$3:J$27,B3 & ",*",K$3:K$27,"x")+COUNTIFS(J$3:J$27,"*, " & B3,K$3:K$27,"x")+COUNTIFS(J$3:J$27,"*, " & B3 & ",*",K$3:K$27,"x"))>0,"x",""))

The two columns were added to columns J and K in Sheet1 ALT in the attached workbook.
GanttWithPredecessorsQ28706007--2-.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.