Forum Discussion
how would I get the cell to go red if the date input is greater than a year from present day?
Scott_H_7
How about using Conditional formatting on the Home Tab?
Select the area that you want to apply this behavior to.
On the Home Tab, select Conditional Formatting - > Highlight Cells Rules -> Greater Than...
Add "=Now() + 365" or "=TODAY() + 365" into the left "textbox" and select preferred color formatting in the right Dropdown.
=Now() gives you the current time. ie. 2022-06-16 14:00
=TODAY() gives you the starting time of today. ie. 2022-06-16 00:00
365 is number of days and gives the same time 365 days ahead.
with decimals it will look at a different time of the 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 regardHi 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
