Forum Discussion
how would I get the cell to go red if the date input is greater than a year from present day?
Hi Prefrom,
Apologies, explanation over text is sometimes not correct or easy to convey.
What I have is a spreadsheet and I send this out to client/customers to fill in some dates. I have x6 different columns with;
Daily,
Weekly,
Monthly,
Quarterly,
6 monthly,
& Annual,
What I need from them is to submit their dates for the last round of testing for each discipline, I have items in x33 rows.
What I can do and get working is;
Conditional formatting -> Highlighted Cell Rule -> Less Than ->Format cells that are Less than: =TODAY()+365 with light red fill -> this produces a red box, as soon as I put a date of the future in, it has no fill – if it’s a date a few days past it stays read.
What I need to get working and cant;
What I am looking to do is, have a (blank/no fill looking) cell formatted so that when they insert a date and it is over a year old from (todays date) e.g. 5/2/22, I would like that box to go red with the text remaining black – just to highlight that these tests are now overdue.
I hope that makes sense a bit better?
I appreciate all the help in this matter.
Regards,
Scott
This formula also tests for blank
= (D6<EDATE(TODAY(),-12))*(D6>0)
[subtracting 365 gives almost the same result depending on the leap-year]