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

William Kennedy

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.

Yogesh Patel, MBA

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

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

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

William Kennedy

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

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!

William Kennedy

Thanks greatly appreciated