Welcome!

IF statement to update RAG status

Hi there,

I would like to update my RAG Status icons (Green, Red, Yellow, and Grey balls) using a formula based on Due Date, Revised Due Date, and Submission Date inputs. My formula is as follows, and currently generates a #INVALID error message:

=IF([Submission Date]1 > 0, "Green", IF([Revised Due Date]1 - [Due Date]1 > 0, "Gray", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Not Started")))))

Any ideas on what I am doing wrong? My Due Date, Revised Due Date, and Submission Date columns are all in Date format. My statement also seems to work fine when I remove the first Submission Date part of the formula.

What I want it to do is go green if there is a submission date (regardless of whether the item was late or not), go gray if there is a revised due date and no submission date (regardless of whether the item was late or not), go yellow if it is due today and no revised due date or submission date has been populated, go red if the due date was in the past and no revised due date or submission date has been populated, and say "Not Started" if the due date is in the future and a revised / submission date has not been populated.

Thanks!

Find more posts tagged with

Comments

L_123

you can't compare a number to a day without separating the date (In your current formula you do this in the first 2 if statements). Here is an updated version of your formula that works, though you will need to edit it to make it do what you want

=IF(ISDATE([Submission Date]1), "Green", IF(NETDAYS([Revised Due Date]1, [Due Date]1) > 0, "Gray", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Not Started")))))

RobynTocco

thanks Luke_TK. It is going green now when I have a submission date, but is giving me a #invalid error message rather than defaulting to the false part of my first IF statement... How do you get the formula to default to the rest of the IF statement?

L_123

I just tested it and the if(isdate() defintely pushes to false on a blank cell. I think it is probably a typo or error in the next if statement that is being caught and pushing the error. can you post your updated formula?

RobynTocco

Ah got it sorted! Thanks for the help!

Sign in to join the conversation!

Quick Links

Trending discussions

Highlight changes icon, print icon and view option no longer appear in published sheets
I have a number of published sheets, and in recent weeks the bar just below the sheet name, which contained the print icon, the view drop-down menu and the highlight changes icon have disappeared. I haven't done anything on the back end to the underlying sheets. Unfortunately I've put a ticket in for support help (on about…
Smartsheet Support Issue
I'm currently having issues with my Smartsheet environment. I pay $72/mo for 3 licenses when I only need one, yet when I have issues my support ticket has remained open for 2 weeks without being assigned. I've looked for help within the community, however when I posted my question a simple response by a random user marked…
How to avoid duplicate entries while submitting from forms
I want to restrict the duplicate entries in the sheet based on some column value conditions, I can be able to highlight the column but is there any direct method for avoiding duplicates.