Forum Discussion
Date that will automatically highlight after 2 weeks
Hi,
I have a spreadsheet with a column of dates that I would like to automatically highlight after 2 weeks.
The date column is not sorted as my list is alphabetical according to the name column.
Is there a way to do this? I have attached a page from my spreadsheet for refernce.
Thanks!
Yes. To illustrate I added current date as =TODAY() (now is Fri, Mar 12) and date two weeks before (Fri, Feb 26). All dates which are older will be highlighted
9 Replies
- If you put =today() in B1 and all of your future dates are in A3:A22 you could highlight using the conditional format formula: =(A3-$B$1)<=14 To apply that formula. Select the range you have your dates in > On Home ribbon click conditional formatting, >click "Use formula to cnoose which cells to format" > copy my formula above into the formula bar > Choose your format in the formatting dialogue > OK > OK It's usually best to set up a helper column adjacent to your dates and test your conditional formula first. Hope that helps more than it confuses and that it is what you want.
Norman_Harker , you forgot to exclude blank cells, and that's an opposite - in the range are dates in past. Highlighted are to be cells with more than two weeks old dates.
- Agreed! Always unsafe to assume all cells have entries.
Similar on > rather than >=
That's why I prefer to set up the 'helper' column first and establish by TRUE and FALSE whether the conditional format formula will do what I want with the data that I have.
Only the user knows precisely what they want and have to cover for.
Regards and keep yourself, family and friends safe.
Norman
You may apply conditional formatting rule with formula as
On my PC dates were actually texts, I converted them to date values with Data->Text to Columns wizard.
Please check in attached file.
- Thank you for answering! Just to make sure I understand (because I thought that was it but wasnt sure) Using that formula, if I enter a date today into my speadsheet, it will autmatically turn red in 2 weeks from the date entered?
Yes. To illustrate I added current date as =TODAY() (now is Fri, Mar 12) and date two weeks before (Fri, Feb 26). All dates which are older will be highlighted
