RAG Status Indicators 1

[GarethWhite]

Hi all,

I am attempting to generate a RAG status indicator that shows the following formula:

If task is approaching target completion date (i.e. 20% time from completion date)and the % complete is >80% then the RAG should show green.
If the % complete is between 50% and 80% then show amber and <50% show red.

I would like to use this to be able to see at any point very quickly whether we are at risk of becoming behind schedule by using the above. Can anyone help with the formula?

 

[PDQBach]

%Completion refers to the % of elapsed time since the task began. When you are at, for example, the 8th day of a 10 day task, you will be at 80%Completion. Therefore, your stoplight will always show amber.

 

[PDQBach]

It occurred to me later that you were using %Complete as a proxy for Actual Work. So I played around a bit trying to come up with a formula using Now(), Start, Duration and %Complete. I couldn't come up with anything that I would want to share. I'll keep playing but your requirements seem to make the formula somewhat daunting.

 

[PDQBach]

Well, I kept playing. I couldn't come up with a really good formula -- the situation is just too complex for something like that. I was able to throw together some VBA but Gareth hasn't been on for a while so I suspect he's lost interest.

 

[GarethWhite]

Thanks PDQ - I have been checking and kind of guessed from your previous comment that it might have been a little more difficult than I had first thought. Dont want to cause anyone too much hassle - was only if there was a simple solution out there that I had missed.

Really appreciate your help in investigating though.

Kind Regards

Gareth

 

[PDQBach]

If you want to go the VBA route, here's some code to get you started (watch out for line wraps):

Sub PDQBachStoplight()

Dim tsk As Task
Dim lngMinutes1 As Long
Dim lngMinutes2 As Long
Dim lngPercentage As Long

For Each tsk In ActiveProject.Tasks
    If Not tsk Is Nothing Then                  ' Ignore blank rows
        tsk.Text10 = "white"                    ' Clear out any previous values
                                                ' Change "white" to "" in production
        If Not tsk.Milestone Then               ' Ignore milestone tasks
            If tsk.PercentComplete < 100 Then   ' Process tasks that are not complete
                If tsk.Start < Now() Then       ' Ignore future tasks
                    'How much progress (elapsed duration) towards Finish date?
                    'Step-by-step calculation so others can easily see what we're doing
                    lngMinutes1 = DateDiff("n", Now(), tsk.Start)
                    lngMinutes2 = DateDiff("n", tsk.Finish, tsk.Start)
                    lngPercentage = (lngMinutes1 * 100) / lngMinutes2
                    tsk.Number10 = lngPercentage
                    If lngPercentage > 100 Then
                        ' if lngPercentage > 100 then task is late (force to Red)
                        tsk.Text10 = "red"
                    Else
                        If lngPercentage >= 80 Then
                            ' Not late
                            Select Case tsk.PercentComplete
                                Case 0 To 49
                                    tsk.Text10 = "red"
                                Case 50 To 79
                                    tsk.Text10 = "amber"
                                Case Else
                                    tsk.Text10 = "green"
                            End Select
                        End If
                    End If
                End If
            End If
        End If
    End If
Next
                                
End Sub