Is there a way of conditional formatting cells with dates with a hyperlink attached.

Tayla

I have conditional formatted all cells to fill green when the date is in the past 730 days (2 years), and to fill red when the date is before 2 years ago. Those are the only two formats I have for every cell. Now I want to add a hyperlink to each cell. I use the CTRL+K function, type the URL and display the same text as what was in there already. The hyperlink attaches but then the cell turns white, and the conditional format no longer is active. I believe the hyperlink makes the format not recognize the data as a date and therefore cant format, as I've attempted with other formats for letters or numbers and this doesn't seem to happen. Is there another way I could format the dates that could still be recognized when using hyperlinks.

My format is - If column 1 is in the past 730 days, fill green for column 1

and - If column 1 is in the past, fill red for column 1.

(The first rule overrules the second)

Paul Newcome

It is because the Display Text is a text string. It is not stored as a date and as such can't be used as a date. Since a text string cannot be less than or greater than a date, it won't be read for your Conditional Formatting.