How do I set up the RAG status to automatically populate based on target end date vs actual end date

johnna.young

I'm trying to get the red, yellow or green status to automatically populate based on the difference in days for target end date and actual end date?

If the actual end date is 10 days or more past the target end date, it should be red.

If the actual end date is more than 1 day but less than 10 days past the target end date, it should be yellow.

If the actual end date is the same or earlier than the target end date, it should be green.

Please help.

Carson Penticuff

You will need to adjust for actual column names, but this should work.

=IF(AND(ISDATE([Target End Date]@row), ISDATE([Actual End Date]@row)), IF([Actual End Date]@row >= [Target End Date]@row + 10, "Red", IF([Actual End Date]@row > [Target End Date]@row, "Yellow", "Green")))

KPH

Hi @johnna.young

Here are a couple of assumptions

You want to count calendar days and not working days

I can change the second clause to make sure you have a color for if the actual end date was exactly 1 day after the target:

• If the actual end date is more than 1 day or more but less than 10 days past the target end date, it should be yellow.

Here is the formula (you might need to change the column headings)

=IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green", "Yellow"))

Here is how it looks with some sample data

Here is how I made it

Start by calculating the difference between the dates using

=[Actual end date]@row - [Target end date]@row

Then add an IF around that calculation to set up your "Red"

=IF([Actual end date]@row - [Target end date]@row >= 10, "Red")

(in other words, if the actual end date minus the target end date is 10 or more put a red icon)

Then add an another IF to set up your "Green" and put this in the position to be evaluated if the first IF is not true

=IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green"))

(in other words, if the actual end date minus the target end date is 10 or more put a red icon, if not but if 0 or less put a green icon)

You could then make everything else amber using this:

=IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row -One [Target end date]@row <= 0, "Green", "Yellow"))

(in other words, if the actual end date minus the target end date is 10 or more put a red icon, if not but if 0 or less put a green icon, and if not 0 or less put a yellow icon)

Bonus points

One additional thing you may want to do is wrap the whole formula in another formula so the icons only appear if both Target end date and Actual end date are populated.

This will put nothing in the column if either of the dates are blank:

=IF(ISBLANK([Actual end date]@row - [Target end date]@row), "", IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green", "Yellow")))

This is similar but will put a gray icon:

=IF(ISBLANK([Actual end date]@row - [Target end date]@row), "Gray", IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green", "Yellow")))