SOLVED

Excel Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2917958%22%20slang%3D%22en-US%22%3EExcel%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2917958%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20a%20date%20which%20less%20than%20another%20date%20to%20be%20highlighted.%26nbsp%3B%26nbsp%3B%20Each%20date%20neither%20of%20which%20is%20todays%20date%2C%26nbsp%3B%20will%20change%20with%20the%20passage%20of%20time.%3C%2FP%3E%3CP%3EDo%20I%26nbsp%3B%20use%20formatting%20or%20Conditional%20formatting%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2917958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2917989%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2917989%22%20slang%3D%22en-US%22%3EConditional%20formatting%20should%20do%20it.%20Otherwise%20you're%20talking%20of%20manually%20keeping%20it%20up-to-date.%20If%20you're%20looking%20for%20help%20in%20establishing%20the%20rule%20for%20conditional%20formatting%2C%20come%20back%20and%20ask%20that%2C%20ideally%20with%20a%20sample%20workbook.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2923009%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2923009%22%20slang%3D%22en-US%22%3EMathetes%20thanks%20for%20yor%20response%3CBR%20%2F%3EI%20have%20tried%20Condition%20Formatting%20using%20Highl%C3%ADght%20Cell%20Rules%2C%20Less%20than%20without%20success.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20data%20base%20of%20building%20material%20costs%20I%20want%20to%20keep%20tract%20on%3CBR%20%2F%3Ethose%20out%20of%20date%20by%20moving%20todays%20date%20back%20by%20various%20time%20periods.%3CBR%20%2F%3E%3CBR%20%2F%3ETodays%20Date%203.11.21%3CBR%20%2F%3E%3CBR%20%2F%3EMove%20todays%20backwords%20by%20months%202%20%3D%2060%20Days%204.9.21%3CBR%20%2F%3EBackward%20movement%20may%20change%20with%20market%20conditions%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Enqiry%20date%20is%20more%20than%20red%20date%20above%2C%20Show%20no%20background%20colour%3CBR%20%2F%3EIf%20Enqiry%20date%20is%20less%20than%20red%20date%20above%2C%20Show%20it%20with%20red%20background%3CBR%20%2F%3E%3CBR%20%2F%3EEnquiry%20date%20which%20will%20change%20as%20it%20is%20backward%20looking%203.10.21%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20a%20rookie%20w%C3%ADth%20computers%2C%20so%20am%20not%20sure%20how%20to%20attached%20a%20typical%20spread%20sheet.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2923028%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2923028%22%20slang%3D%22en-US%22%3EUse%20the%20full%20Text%20editor%20option%20(down%20below%20the%20dialog%20box%20that%20opens%20when%20you%20click%20on%20%22Reply%22)%20and%20drag%20and%20drop%20your%20file%20to%20the%20area%20set%20aside%20for%20that%20purpose.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2923051%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2923051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2923085%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2923085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1204313%22%20target%3D%22_blank%22%3E%40John_P381475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%22The%20whole%20file%20is%20to%20large%20to%20attach%22%20-%20we%20don't%20need%20the%20file%20with%20actual%20data%2C%20we%20need%20the%20file%20which%20illustrates%20how%20data%20is%20structured.%20Conditional%20formatting%20applied%20to%20only%20one%20cell%20that%20could%20be%20one%20rule%2C%20applied%20to%20entire%20range%20-%20another%20rule.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I want a date which less than another date to be highlighted.   Each date neither of which is todays date,  will change with the passage of time.

Do I  use formatting or Conditional formatting

11 Replies
Conditional formatting should do it. Otherwise you're talking of manually keeping it up-to-date. If you're looking for help in establishing the rule for conditional formatting, come back and ask that, ideally with a sample workbook.
Mathetes thanks for yor response
I have tried Condition Formatting using Highlíght Cell Rules, Less than without success.

I have a data base of building material costs I want to keep tract on
those out of date by moving todays date back by various time periods.

Todays Date 3.11.21

Move todays backwords by months 2 = 60 Days 4.9.21
Backward movement may change with market conditions

If Enqiry date is more than red date above, Show no background colour
If Enqiry date is less than red date above, Show it with red background

Enquiry date which will change as it is backward looking 3.10.21

I am a rookie wíth computers, so am not sure how to attached a typical spread sheet.
Use the full Text editor option (down below the dialog box that opens when you click on "Reply") and drag and drop your file to the area set aside for that purpose.
best response confirmed by John_P381475 (Occasional Contributor)
Solution

@John_P381475 

"The whole file is to large to attach" - we don't need the file with actual data, we need the file which illustrates how data is structured. Conditional formatting applied to only one cell that could be one rule, applied to entire range - another rule.

@John_P381475 

 

It works in this version. For what it's worth, I've always found the conditional formatting rules with dates to be tricky. It takes me multiple tries to get them to work as desired.

Brief file previously attached shows the enquiry date which will apply to more than a hun dred rows on a sheet

@John_P381475 

 

Not sure what your point is. I gave you a conditional formatting rule that worked. You don't acknowledge it in your post, which was over an hour after mine....strange. Was it not working when you downloaded my version?

@mathetes 

Sorry for delay in response

Tried what you said on attached file with no success

I noted someone said workíng with dates is a problem.

As I think I said previously I am a rookie at computing, trying to lean at over 70.

Are you able to provide líne by line what to do?

 

@John_P381475  wrote: 

Tried what you said on attached file with no success

 

But I didn't "say" anything.  I gave you a Conditional Formatting rule that worked, didn't it?

 

I noted someone said workíng with dates is a problem.

 

That was me. I said that I've always found them tricky or elusive, so I need to experiment.

 

As I think I said previously I am a rookie at computing, trying to lean at over 70.

 

I turned 80 myself, very recently. I appreciate the difficulty you may be experiencing, especially if you're learning Excel for the first time.

 

Are you able to provide líne by line what to do?

 

First, I'd like to know whether or not the file I sent back to you worked. That file is attached again, at the bottom of this window. If it works, you should be able to open the Conditional  Formatting dialog box and copy the rule (or the syntax of the rule) to your own situation.

 

And here, to help you, is the rule written in that Conditional Formatting dialog box.

mathetes_0-1636076710249.png

 

@John_P381475 

 

Hey, John...here's a resource that you might find helpful. This website in general is a good one to bookmark, because the owners of the site give good examples of how to use many of the functions and features of Excel. I've linked you here to a page on Conditional Formatting with dates.

https://exceljet.net/formula/conditional-formatting-date-past-due