Excel 2007 : Icon Sets for dates

**ambermarie** · 11-18-2009, 12:02 AM

Hi,

I am trying to use conditional formatting to set up icon sets (3 traffic lights). In one column I have Start Date, Finish Date, Today's Date, and # of Days til Start Date.

I have a list of tasks which have a due date (these dates change as the current date changes).

What I am trying to do is have:
a red icon appear if the due date is within 9 days of Start Date OR overdue,
an orange icon within 10 to 27 days of Start Date or
a green icon if it is 28+ days of Start Date.

Can anyone help?

**Paul** · 11-18-2009, 12:22 AM

It seems like you have it all figured out, honestly.

Select the cell with the # of days until start date and click Conditional Formatting. Choose 'Icon Sets' -> select the 3 Traffic Lights option.

With that cell still selected, click Conditional Formatting again, and choose Manage Rules, then click 'Edit Rule...'

To the right of Green, change the Value to 28 and change the Type drop-down to Number.
To the right of Yellow, change the Value to 10 and change the Type drop-down to Number.
To the right of Red, you don't need to do anything.

Click OK.

**ambermarie** · 11-18-2009, 02:12 AM

Hi Paul,

Thank you for your help.
I think I may have communicated it incorrectly - my bad.

I am trying to apply the icon sets to the Due Date of each task (in total 105 tasks). Not just the the one cell.

What I have in the Due Date column is =G13-28 (G13 is Start Date). So if the current date is still 30 days til Start Date then there should be a Green icon.
If the current date is 3 days til Start Date then there should be a Red icon.

I thought I had to input an IF statement in the 'value' cell in the Conditional Formatting window - but it comes up with an error.

If I use numbers in the 'value' cell then all the icons are green in the Due Date column.

Does that make sense?