Need correct syntax for IF and logical "AND" to properly set the status to Blue/Red/Yellow/Green

Objective is to have the correct syntax for IF and logical "AND" to properly set the status to Blue/Red/Yellow/Green, where the fields/columns to test are:

Start Date,

End Date,

Status ("Not Started", "In Progress", "Complete")

This may take a few iterations to communicate the proper scenarios or conditions of the status value of: Blue/Red/Yellow/Green.

Thanks in advance, Bill Kennedy.

Answers

  • Hi Bill,

    I have the solution for you which will allow you to have color symbol status changes according to the start and end dates. Let's do a bit of column setup in the sheet first:

    You will need the following columns:

    1. Start date - Set default as date. Information will be entered by the user
    2. End date - Set default as date. Information will be entered by the user
    3. Status_word - This will be populated via an IF formula and the column will be hidden
    4. Status_syb - This will be populated by an IF formula and locked

    After having setup the columns, here is the IF formula you will need in the cells the Status_word column:

    =IF(AND(ISDATE([Start date]@row), ISDATE([End date]@row)), "Complete", IF(ISDATE([Start date]@row), "In progress", "Not started"))

    Explanation: The first IF statement check if Start date AND End date have a date, if true then enter "Complete", otherwise, check IF Start date alone has a date, if true then enter "In progress", otherwise, if there is nothing in Start date and End date enter "Not started".

    Finally, we setup the formula for the Status_syb column so the colored symbols automatically change:

    =IF(CONTAINS("Complete", [Status_word]@row), "Green", IF(CONTAINS("In progress", [Status_word]@row), "Yellow", "Red"))

    Explanation: Check if the row in Status_word contains "Completed", if true then enter "Green" (which is the required wording for the green symbol), otherwise, check if Status_word contains "In progress", if true then enter "Yello" (which is the required wording for the yellow symbol), otherwise anything else in Status_word is treated as "Red".


    I hope this helps, you can copy and paste this in a blank sheet and test out how the coding works and apply it to your own sheet. Or, you can just created the same columns as the code and copy and paste the code to the respective column cells.

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

    Here is a sample:

    I don't know what you want Blue to be but you can just add it to the and statement.

    Not started: Red

    In Progress: Yellow

    Complete: Green

    =IF([Start Date]@row > TODAY(), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row <= TODAY()), "Yellow", "Green"))

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

    If you want the words instead of the color just replace colors with Words inside the quotation marks.

  • Thanks. I also need to check if the Status has been updated by the Assigned to person as I need to make sure if a task should have started last week, then the Status should be "In Progress" and NOT "Not Started".

    I am trying to have ONE IF and Logical AND statement.

    Thanks, Bill Kennedy

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    Hi, Bill.

    I would begin by identifying the criteria that satisfy each status...

    BLUE = Blue_Criterion01, Blue_Criterion02, Blue_Criterion03

    RED = Red__Criterion01, Red_Criterion02, Red_Criterion03

    YELLOW = Yellow__Criterion01, Yellow_Criterion02, Yellow_Criterion03

    GREEN = Green__Criterion01, Green_Criterion02, Green_Criterion03

    You'll be using nested IF() statements in your formula. To keep the formula easier to read, you can use "helper" columns like in OPTION I below.

    OPTION I

    Create 4 separate columns to perform the test/evaluation: COL_BLUE, COL_RED, COL_YELLOW, COL_GREEN.

    In each column create an IF formula to evaluate for the status. For example, in the COL_BLUE cell for the record/row, your test would be something like: 

    Formula in COL_BLUE

    =IF(AND(Blue_Criterion01, Blue_Criterion02, Blue_Criterion03), "Blue", "")

    And then so on...

    Formula in COL_RED

    =IF(AND(Red_Criterion01, Red_Criterion02, Red_Criterion03), "Red", "")

    Formula in COL_YELLOW

    =IF(AND(Yellow_Criterion01, Yellow_Criterion02, Yellow_Criterion03), "Yellow", "")

    Formula in COL_GREEN

    =IF(AND(Green_Criterion01, Green_Criterion02, Green_Criterion03), "Green", "")

    Then, in the column where you'll be setting the status, your formula would be:

    = JOIN([COL_BLUE]@row:[COL_GREEN]@row)

    You can lock and hide the helper columns. For housekeeping, I add "sys" to column names that I want others to ignore: "sysCreateDate", "sysCreateBy", "sysOK2Archive", etc.

    OPTION II

    Without the helper columns, your IF formula will be something along the line of...

    =IF(AND(Blue_Criterion01, Blue_Criterion02, Blue_Criterion03)
    	,"Blue"
    	,IF(AND(Red__Criterion01, Red_Criterion02, Red_Criterion03)
    		,"Red"
    		,IF(AND(Yellow__Criterion01, Yellow_Criterion02, Yellow_Criterion03)
    			,"Yellow"
    			,IF((AND(Green__Criterion01, Green_Criterion02, Green_Criterion03)
    				,"Green"
    				,""
    				)
    			)
    		)
    	)
    

    Cut-pasted into the sheet, the formula will look like...

    =IF(AND(Blue_Criterion01,Blue_Criterion02,Blue_Criterion03),"Blue",IF(AND(Red__Criterion01,Red_Criterion02,Red_Criterion03),"Red",IF(AND(Yellow__Criterion01,Yellow_Criterion02,Yellow_Criterion03),"Yellow",IF((AND(Green__Criterion01,Green_Criterion02,Green_Criterion03),"Green",""))))

    There are other approaches, too, but OPTION I is the easiest to troubleshoot.

    Hope this helps!

  • Thanks greatly appreciated

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!