Forum Discussion
Solved
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...
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
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.
NormanHi Norman, thanks for the answer!
This is a very simple spreadsheet with no other formulas. I enter the information about the containers we rent, with the name, date of rental, and container number, and that’s pretty much it. I have due dates, so wondered if there is a way for the date column to remind me that the container is due to be picked up after 2 weeks...eg, the cell changing colour.....
