Comparison of columns using Conditional Formatting
A1 and B1: data matches, nothing has changed. No cell shading
A2 and B2: no match (new data) shade B-cell PINK to indicate
a revision or reforecast
A3 and B3: no match (updated data) shade B-cell PINK to
indicate a revision or reforecast
A4 and B4: shade B-cell GREY to indicate a completion older
than 7 days
A5 and B5: shade B-cell BLUE to indicate a completion within
in the last 7 days
With column B selected, something like these formulas in this order might work.
BLUE fill for
=AND(A1="",ISNUMBER(B1),B1=TODAY()-7)
GRAY fill for
=AND(A1="",ISNUMBER(B1),B1<TODAY()-7)
PINK fill for
=A1<B1
Notice that ISNUMBER is used to detect dates. In Excel, a date is just a number formatted in a special way. Since you wrote "'data1' and 'data2' may be text or dates," non-date numbers are ruled out, making the solution easier.
Also, it wasn't clear what's expected when DATE is exactly 7 days old. Make appropriate adjustments if I guessed wrong.
Hope this helps getting started.
(I have Excel 2010.)
|