Date that will automatically highlight after 2 weeks

[Marie_RC]

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!

[#2]

@Marie_RC 

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.

[#3]

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?

[#4 · Accepted solution]

@Marie_RC 

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

[#5]

thank you!
I will give it a try!

[#6]

@Marie_RC , you are welcome

[#7]

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.

[#8]

@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.

[#9]

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

[#10]

@Norman_Harker 

Hi 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.....