Forum Discussion
how would I get the cell to go red if the date input is greater than a year from present day?
Thanks for the detailed response - I have tried this and it is not working - however I tried the LESS THAN and it worked ?!
Scott_H_7
Hm.. "date input is greater than a year from present day" was this meant as in the past?
In that case, I would have thought LESS THAN would need "=TODAY() - 365" in the dialog.
I'm just happy you solved it!
Best regard
- Scott_H_7Jun 17, 2022Copper Contributor
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
- PeterBartholomew1Jun 17, 2022Silver Contributor
- Scott_H_7Jun 17, 2022Copper Contributor
Thanks for the response...I have tried this and still it is not working for me.
The below is how my sheet looks at the moment - client - customer fills in the blue shades
This is how I would like it to look once it has been filled in - I've only filled a couple details and sadly filled the red in.
Again I appreciate all your help! 🙂
Cheers,
Scott
- PeterBartholomew1Jun 17, 2022Silver Contributor
This formula also tests for blank
= (D6<EDATE(TODAY(),-12))*(D6>0)
[subtracting 365 gives almost the same result depending on the leap-year]