Formula for a conditional Formatting using Icon sets

Hi all,

I need help on a formula that will work whilst using it in the conditional formatting icon sets ruling?

Basically, I have Install dates, and then worked on dates, I need to indicate if the work has been carried out within warranty (within a year of install). If the work has been carried out within this timeframe I would like it to indicate the date with a Green Tick along side the date, if not a red cross.

I have been trying to use the following formula, which works in excel but not in conditional formatting for some reason?!?!?

=if(worked on date>(install date+365),"Y","N") this in excel works fine, but when I take out the ,"Y","N" part it does seem to work in conditional formatting?

Any suggestion on how to get this to work?

I have attached an example for you play wiht.

Cheers in advance for your help

Donna

I think you could be right JosephP!!!

Fotis1991, are you able to do this showing tick and cross icons instead of colour, just trying to be a bit more imaginative with my reports!!!

cheers

Donna

---------- Post added at 11:14 AM ---------- Previous post was at 11:05 AM ----------

No worries on the Tick thing, as there doesn't seem to be a way to disregard the 'Amber !' fromt he equation.

so I shall just use the highlighting of cells as suggested by Fotis1991.

Cheers

Donna

thats great JoesphP, but the client requires to see the Date along side the Icon, but I am sure I can use this in another report.

cheers

Donna

Excellent guys both work.

Next question, any idea how I can copy these to other cells without the conditional format anchoring to the original Install date? Everytime I remove the $ from the conditional format, it won't let me carry on!! I have 52 columns and 300 rows to copy these into, and its extremely time consuming doing them 1 at a time!!!

cheers for your help

Donna

Originally Posted by JosephP

just fill the cf cells down.

example of right and wrong.xlsx

I hope this might get you started.