How to conditional format Excel 2010 to trigger flag icon in new column, based on due dates

5
votes

I have a row of deadline dates, I'm looking to flag with icon if deadline is past due date. Is there a way to use conditional formatting to trigger icons? The if then statement should be:

If Today() > "Deadline" then flag cell in column 2 with red flag icon.

Column 1 Deadline

Column 2 (Red Flag if past due date)

2
excelexcel-2010
Whoever hit my reputation for asking this question, wtf – RCoy1978
Do you specifically require a red flag icon, or would a small red-colored cell suffice? – Iakovosian
WTF indeed! Sorry, just a slip of the finger. I had to then edit to fix. – Doug Glancy
+1 by the way, for an interesting question. – Doug Glancy
Lako, I would like to have the red flag. I did something like this, but it is not working properly: =IF((F72-10)<TODAY()<F72,0,1) and then input the conditional formatting icon setting to populate the red flag in a seperate cell. I say it's not working because I copied down the formula, and the same date further on down the cell flags green, when the same date preceding it flags red – RCoy1978

2 Answers

7
votes

I did it like this:

1st: Added a formula to the Flag column: =IF(A2>TODAY(),1,0) 2nd: Conditional formatted flags as show in picture.

enter image description here

3rd: Custom format in cells to hide 1's and 0's. The format is ;;;.

enter image description here

1
votes

The red flag seems to be available only in the cell you are trying to format, not in an adjacent cell.

You can try that.

Select your deadline row.

Add a Conditional Formatting

Format all cells based on their values Select Icon Sets in Format Style Select 3 Flags in Icon Style Tick Reverse the Icon Order On the red flag select > enter =today() in value and Formula in type On the orange flag select > enter =today() in value and Formula in type

You will end up with a red flag in the deadline cell if the date > today. But unfortunately a green flag if date <= today.