Date conditional formatting using Icons set

[flighty]

Hi... like the icon set in conditional formatting .... but do not have a clue how to get it to do what I want... can someone help

My spreadsheet has dates in column F and column G

In column F is the last date the work was done and
In column G is the next date the work is due

I would like column G to show:

a green icon when the date inserted in G is more than 3 months from the date in column F

a yellow icon when the date inserted is within a 3 month span between column F and G

and

a red icon when the date inserted is beyond the date inserted in column G

Thanks.... Excel 2007 or 2010

 

[cyrilbrd]

Hi,
I think you can use conditional formatting "Icon Set" for this, but I am not sure about your range.
Given the following data:
[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]last date[/TD]
[TD="width: 65"]next date[/TD]
[TD="width: 65"]range[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1/1/12[/TD]
[TD="class: xl63, align: right"]4/1/12[/TD]
[TD="align: right"]91[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1/1/12[/TD]
[TD="class: xl63, align: right"]2/1/12[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1/1/12[/TD]
[TD="class: xl63, align: right"]1/2/12[/TD]
[TD="align: right"]1[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

The first set of dates is 91 days apart so it would fall in your "green"?
The second set of dates is 31 days apart, would you consider than being within a 3 month span hence "yellow"?
What would be "within" it is 20 to 40 days? both being 30 days plus minus 10 days...
Thanks to clarify.

 

[flighty]

Hi,
I think you can use conditional formatting "Icon Set" for this, but I am not sure about your range.
Given the following data:
[TABLE="width: 195"]
<tbody>[TR]
[TD="width: 65"]last date
[/TD]
[TD="width: 65"]next date
[/TD]
[TD="width: 65"]range
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1/1/12
[/TD]
[TD="class: xl63, align: right"]4/1/12
[/TD]
[TD="align: right"]91
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1/1/12
[/TD]
[TD="class: xl63, align: right"]2/1/12
[/TD]
[TD="align: right"]31
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1/1/12
[/TD]
[TD="class: xl63, align: right"]1/2/12
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

The first set of dates is 91 days apart so it would fall in your "green"?
The second set of dates is 31 days apart, would you consider than being within a 3 month span hence "yellow"?
What would be "within" it is 20 to 40 days? both being 30 days plus minus 10 days...
Thanks to clarify.

I would like to try this out but have no clue how to put in the formula.... can you assist.... I am using Excel 2010 for first time.... thanks I would really appreciate a step by step help....

 

[cyrilbrd]

Ok, please check those links, they might help you.
link1.
link2.
link3.

Link2 and 3 are more on what you want to achieve.
You could use "Number" as Type.
90 and 30, although you may want to change the 30 to a higher value based on your goal. the last value is always 0.
considering the above 91 would be green, 31 would be yellow and 1 would be red. Again you can change the values to fit your specific model.

Cheers.

 

[flighty]

Ok, please check those links, they might help you.
link1.
link2.
link3.

Link2 and 3 are more on what you want to achieve.
You could use "Number" as Type.
90 and 30, although you may want to change the 30 to a higher value based on your goal. the last value is always 0.
considering the above 91 would be green, 31 would be yellow and 1 would be red. Again you can change the values to fit your specific model.

Cheers.

It needs to be caluculating the date from the date set in colum F to that which is input in column G and although I have Number as a type and put in the as green >= 90 amber <90 and >= 30 and red <30 it does not work...

I am thinking because it has no formula to calculate the number of days between the date in F and the date in G.....

I must be missing a step and a formula and I do not see where or how to add this in.....



I do have a green icon in the cell in column G so have something working.... but even if I change the dates it stays green

 

[cyrilbrd]

The cond form needs a reference, you can use a formula to reference both cells (date in G - date in F).
Then set your icon based on the value in said cell.