# Formula, MS Project Rag by Finish Date - Help!!!

• ### Question

• Hello

I have had a look about and can only seem to find "stoplight" ro rag indicator formulas using % complete as part of the formula. What I am trying to do I think is simpler than that (ps my project and excel skills leave a lotto be desired!)!

I want to have a graphical rag indicator as below

If finish date > 3 days from the current date = Green

If finish date < 3 days from current date = Amber

If finish date > current date + 1 = Red

Any formula whizz's out there, advice welcome

Thanks

﻿﻿﻿﻿

Tuesday, October 30, 2012 9:48 PM

### All replies

• Emma

The rules you have laid out wont work for you. If today is monday (day 1) and the task is scheduled to finish on thursday (day 4) then both red and green will be true. It will then depend on the order you write the formula in and how you write it as to what you will get, which is not really desireable.

I am struggling to understand what you are trying to achieve with this as the fact that somethng is forecast to finish next week does not in and of itself normally make a measure for RAG status. If you are doing this strictly off dates then it would normally be done as a variance from baseline, say something like

"if the task is incomplete and more than 3 days off baseline its amber, if its incomplete and more than 8 days off baseline its red"

The baseline variance field will do the date arithmetic between baseline and forecast dates (there is an explanation in the help).  To get a feel for this try and create a table with:

ID, Task Name, Finish Date, Baseline Finish Date, Baseline variance and flags 1,2 and 3

Now under Flag 1 set it if baseline variance > 8, set flag 2 if its >3 and flag three if its <=3

Now you can scroll up and down the project and follow the flags and tweak your formulas as needed and you can clearly see what is setting and why.

Hope this helps

Ian Stockdale

Wednesday, October 31, 2012 9:31 AM
• Emma, Ian makes some good points about how well the indicators you describe will actually achieve the business purpose you're looking for. However, as a general approach that you may be able to adapt to your requirements, consider something like this for a formula using a Custom Task Duration field:

ProjDateDiff(now(),[Finish])

This treats weekends and calendar holidays as non-working days. You may want to include an Iif statement or two to avoid indicators for completed tasks, etc.

The indicator settings will look something like:

• is greater than or equal to   3d      24
• is greater than or equal to   1d      23
• is less than                        1d      22

Be careful when working with durations. They are actually stored as working minutes so occasionally you'll need to apply a factor of 480 to convert between minutes and days for manipulation or display.

Wednesday, October 31, 2012 9:57 AM
• Hi,

I know it's a detail, and I know I'm an old man always preaching the same, but using 480 without any comment is a bad advice. In Western Europe many companies do not have an 8 hour working day so using 480 will lead to wrong results. Do use the constant [Minutes per Day] which can be found under Field, Project, Number in the formul window.

Greetings,

Wednesday, October 31, 2012 12:40 PM
• Jan, thanks. A salutary reminder.       Graham
Thursday, November 1, 2012 9:25 AM