Highlighting value, then un-highlighting after a year

[callyleth]

Hi,

I'm not sure if this can be done, but I'm hopeful that someone here can help me

In my spreadsheet, I have names, contract numbers and 4 item columns with dates in them. Ideally, what I would like to happen is the name cell highlights when all the item cells in the row have a value. After the oldest date in the row reaches a year, I'd like it to un-highlight the name cell and highlight the overdue date cell in a different colour. For example, when Dave's row has dates in all 4 item cells, it highlights his name cell green. When the date is 4/3/2020, cell E2 highlights in red and un-highlights Dave's name cell.

I know I'll need to use Conditional Formatting, but what formulas would I need to put in the cells to have them do this?

If this can't be done, what formulas would I use to just highlight the item cell when it has value and then un-highlight when the value is over a year old? For example, cells C2:F2 highlight when I put a date in them, but E2 un-highlights when the date is 4/3/2020

Thanks in advance for any help

[Beamernsw]

Hi Callyleth, I think these formulas should work for you:-

Formatted Red - Applies to: =$C$2:$F$7

Please Login or Register  to view this content.

Formatted Green - Applies to: =$A$2:$A$7

Please Login or Register  to view this content.

Edit:-
These highlites would be thrown out by a day during or after a leap year.

[AliGW]

Why not use EDATE instead? This works with leap years.

Try:

=AND(EDATE(C2,12)-1 < TODAY(),C2<>"")

and:

=AND(COUNTA(C2:F2)=4,MIN(C2:F2) > EDATE(TODAY(),-12))

[davesexcel]

I was trying to post this and kept getting kicked out saying HTML code is not allowed....grrr

[AliGW]

Sometimes you have to put spaces either side of the < or > to get it to be accepted - it's a known issue.

[Beamernsw]

Thanks for the improved formula AliGW, I love seeing functions used that I've never played with before. If only I could remember it all
But that's what this forum is for....to help find stuff we haven't used before...or better yet, finding stuff we have used but used in an unexpected way.. gotta love that :D

[AliGW]

Absolutely! I have learnt pretty much everything I know here.

[callyleth]

Wow! This is great! Thanks for the help from everyone

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.