Conditional Formatting in Excel date range and counting

Question

I have an Excel spreadsheet that has conditional formatting. if more than three of the same value is added to the same column (currently set to D) then it turns red, if more than 1 then it changes to yellow. I want to add a date parameter to this. I want to at =TODAY () -180.

How would i add it to the formula below?

=COUNTIF(D:D,D8)>2

=COUNTIF(D:D,D8)>1

Basically i want it to flag red for more than 2 times in the column if the date in column A is within 6 months of todays date.

so if i add three of the same info into the D column and all the dates in the A column are with the last 6 months it flags red or yellow but after 6 months it then disregards and would change back to yellow or blank as it has not been entered in the last 6 months.

Answer 1

Excel has no inherent way to store the timestamp of the last time a particular cell was modified.

To accomplish what you want, you would have to add a new column (e.g. in column E) which stores a timestamp of when the value in column D was entered. Then you can modify your conditional formatting rules to something like this:

= COUNTIFS(D:D,D8,E:E,">="&(TODAY()-180))>1

On a side note, TODAY()-180 is not the proper way to calculate 6 months ago.

The proper way to do this would be:

= DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))