Simple RAG status

Ollie Cater

Hi all,

I am looking to create a RAG status, based on start date of a task. Rules would be:

1. If start date is over a week away, status is GREEN
2. If it's within one week of the start date is YELLOW
3. If start date is in the past is RED

I also have 3 simple status options, so

1. If status is set to "Complete" I would like the RAG dot to be GRAY.
2. If status is "In Progress" and end date is not yet reached YELLOW

I am not using percentage completion, and most other examples I can find seem to rely on that, but I just need something very straightforward.

Thanks in advance!!

Ollie Cater

You too! Thank you ever so much!

Heather Duff

Hi @Ollie Cater ,

Are you wanting to have two separate formulas, or do you want to combine these two?

To have them separate, they would look like this:

=if([start date]@row>TODAY(7),"Green",if([start date]@row>TODAY(),"Red","Yellow")

=if([status]@row="Complete","Gray",if(AND([status]@row="In Progress",[end date]@row<TODAY()),"Yellow",""))

If you want to combine the two, we'll need to dive a little deeper.

Hope this helps. Let me know if it works for you!

Best,

Heather

Ollie Cater

Hi Heather,

Thanks for the quick reply! Ideally I'd want them both in the same formula if possible..

Thanks in advance!

Ollie

Heather Duff

Hi Ollie,

Let's try this:

=if([status]@row="Complete","Gray",if(AND([status]@row="In Progress",[end date]@row>TODAY()),"Yellow",if([start date]@row>TODAY(7),"Green",if([start date]@row<TODAY(),"Red","Yellow"))))

This translates to:

If status is Complete, GRAY

If status is In Progress and end date is in the future, YELLOW

If start date is more than 7 days past today, GREEN

If start date is before today, RED

Otherwise, YELLOW. (The previous two - red and green - conditions make this "otherwise" account for a start date between today and 7 days past today.)

I'm not convinced this formula is fool proof, but that depends on how long the gap is between your start & end dates. Remember that SS formulas check sequentially, so if condition 1 is met, it stops there. If condition 1 is not met, it moves on to condition 2, and continues down the line until it finds a condition that it does meet.

Let me know if it works!

Best,

Heather

Ollie Cater

Hi Heather!

Unfortunately I seem to be hitting the same roadblock as when I was trying to write my own version of this...#unparseable...

Ollie

Heather Duff

That's strange - it's working for me:

Are your Start Date and End Date columns set as Date columns in their column properties?

Ollie Cater

Hi Heather,

I double checked just to be sure, and yes as far as I can tell the format is set to date/time.

Very confused...

Heather Duff

Can you please provide a screenshot? Block out any sensitive information. Also - try copying and pasting the formula that's returning #UNPARSEABLE so that we can look at it.

Ollie Cater

Here's a very limited grab..

And the code is copied & pasted from your response earlier (the combined code)

Heather Duff

Hmm. I can't see your column headers, but make sure the bracketed items in the formula match your column headers exactly.

Ollie Cater

And SUCCESS!!

Thank you so much for your help!

Heather Duff

Hooray! Glad it worked. Have a great day!

Ollie Cater

You too! Thank you ever so much!