SOLVED

Date that will automatically highlight after 2 weeks

%3CLINGO-SUB%20id%3D%22lingo-sub-2205433%22%20slang%3D%22en-US%22%3EDate%20that%20will%20automatically%20highlight%20after%202%20weeks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2205433%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20a%20column%20of%20dates%20that%20I%20would%20like%20to%20automatically%20highlight%20after%202%20weeks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20date%20column%20is%20not%20sorted%20as%20my%20list%20is%20alphabetical%20according%20to%20the%20name%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%3F%26nbsp%3B%20I%20have%20attached%20a%20page%20from%20my%20spreadsheet%20for%20refernce.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2205433%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206037%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20that%20will%20automatically%20highlight%20after%202%20weeks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F974771%22%20target%3D%22_blank%22%3E%40Marie_RC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20conditional%20formatting%20rule%20with%20formula%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20688px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263178i02DBB4C35B72CFB8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOn%20my%20PC%20dates%20were%20actually%20texts%2C%20I%20converted%20them%20to%20date%20values%20with%20Data-%26gt%3BText%20to%20Columns%20wizard.%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206067%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20that%20will%20automatically%20highlight%20after%202%20weeks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206067%22%20slang%3D%22en-US%22%3EThank%20you%20for%20answering!%20Just%20to%20make%20sure%20I%20understand%20(because%20I%20thought%20that%20was%20it%20but%20wasnt%20sure)%20Using%20that%20formula%2C%20if%20I%20enter%20a%20date%20today%20into%20my%20speadsheet%2C%20it%20will%20autmatically%20turn%20red%20in%202%20weeks%20from%20the%20date%20entered%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206078%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20that%20will%20automatically%20highlight%20after%202%20weeks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F974771%22%20target%3D%22_blank%22%3E%40Marie_RC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes.%20To%20illustrate%20I%20added%20current%20date%20as%20%3DTODAY()%20(now%20is%20Fri%2C%20Mar%2012)%20and%20date%20two%20weeks%20before%20(Fri%2C%20Feb%2026).%20All%20dates%20which%20are%20older%20will%20be%20highlighted%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20468px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263188i9C602ED09B08C121%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

9 Replies

@Marie_RC 

You may apply conditional formatting rule with formula as

image.png

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?
best response confirmed by Marie_RC (New Contributor)
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

image.png

thank you!
I will give it a try!
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

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