Rag Status for end date and % complete

Jez Akali

Hi there,

Could somebody help me with this formula for a rag status please?

The one I'm using at the minute is not picking up red statuses.

=IFERROR(IF([Percentage Complete]@row = 1, "Blue", IF(AND([Percentage Complete]@row < 1, [Finish Date]@row > TODAY()), "Green", IF(AND([Finish Date]@row < TODAY(1), [Finish Date]@row < TODAY(3)), "Yellow", "Red"))), "")

Thanks!

Jez

Darren Mullen

@Jez Akali This issue is here: IF(AND([Finish Date]@row < TODAY(1), [Finish Date]@row < TODAY(3)), "Yellow")

Both of these AND conditions will always be true if the finished date is more than 3 days in the past. You would need to do something like AND([Finish Date]@row < TODAY(1), [Finish Date]@row > TODAY(4))

Jez Akali

Thanks @Darren Mullen unfortunately I'm still getting the same issue

Genevieve P.

Hi @Jez Akali

Just a brief note - the numbers in your TODAY function should be negative if you're looking for days in the past. Try this:

=IFERROR(IF([Percentage Complete]@row = 1, "Blue", IF([Finish Date]@row > TODAY(), "Green", IF([Finish Date]@row > TODAY(-4), "Yellow", "Red"))), "")

The formula will only move to the Yellow statement if the % complete is less than 100%, and if the Finish Date is not in the future. This means we only need to check and see if the date is within the last 3 days (or greater than 4 days ago).

Cheers,

Genevieve

Jez Akali

That's great, thank you so much @Genevieve P. !