RAG Status for Date Ranges


I'm trying to create an extra column in my Project which tracks Finish dates and provides a RAG status as per that date.

EG -

  • If my Task Finish date is today or earlier, then show that task(?) (or the cell in my new column?) as red
  • If my Task Finish date is within the next 3 days, then show it as yellow
  • If my Task Finish date is more than 3 days away then show it as green (or leave blank - TBC!!)

I've looked at Status Indicator but I'm getting stuck with trying the write a formula that will make it work, but it might not be what I'm looking for - or there is a better way.

Any help or advice is greatly appreciated.

Many thanks,



Pete --

Use a custom Text field and enter the following formula:

Switch([Finish]<Now(),"Current",[Finish]<=ProjDateAdd(Now(),"3d","Standard"),"Near Future",[Finish]>ProjDateAdd(Now(),"3d","Standard"),"Distant Future")

Select the Use Formula option in the Calculation for Task and Group Summary Rows section of the Custom Fields dialog.  Then enter the following graphical indicator criteria:

Equals          Current          Red stoplight

Equals          Near Future          Yellow Stoplight

Equals          Distant Future          Green Stoplight

I have tested both the formula and the stoplight indicators, and they work as expected.  Hope this helps.

Dale A. Howard [MVP]

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


Question Info

Last updated November 8, 2019 Views 82 Applies to: