Conditional Formatting Formula for Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2294926%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Formula%20for%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294926%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20write%20a%20formula%20for%20conditional%20formatting%20to%20be%20applied%20to%20two%20columns%20with%20dates.%20Column%20B%20is%20the%20Next%20Shipment%20Date%2C%20and%20Column%20C%20is%20the%20Due%20Date%20of%20the%20order.%20I%20have%20other%20conditional%20formatting%20to%20highlight%20in%20both%20columns%20if%20the%20date%20has%20passed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20I'm%20trying%20to%20use%20conditional%20formatting%20to%20highlight%20cells%20in%20Column%20B%20if%20the%20following%20criteria%20are%20met%3A%3C%2FP%3E%3CP%3E1.%20If%20the%20Next%20Shipment%20Date%20is%20AFTER%20the%20Due%20Date%3C%2FP%3E%3CP%3EAND%202.%20If%20the%20Due%20Date%20is%20NOT%20blank%3C%2FP%3E%3CP%3E3.%20If%20the%20Next%20Shipment%20Date%20IS%20blank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EItem%203%20may%20need%20to%20split%20into%20its%20own%20conditional%20formatting%20rule%2C%20which%20is%20fine.%20However%2C%20I%20think%20items%201%20%26amp%3B%202%20need%20to%20be%20combined%20into%20one%20formula%20to%20identify%20the%20proper%20fields.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20screen%20shot%20below%20is%20from%20my%20data%20showing%20that%20there%20are%20rows%20in%20which%20the%20Next%20Shipment%20Date%20or%20Due%20Date%20(or%20both)%20are%20blank.%20I%20removed%20other%20conditional%20formatting%20from%20this%20sample.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting.JPG%22%20style%3D%22width%3A%20186px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275648iEFC6A3777227021F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting.JPG%22%20alt%3D%22Conditional%20Formatting.JPG%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20screen%20shot%20below%20shows%20what%20I%20would%20like%20to%20highlight%20based%20on%20the%20criteria%20detailed%20above.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting%20Example.JPG%22%20style%3D%22width%3A%20182px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275650iC63603C2EB010E66%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting%20Example.JPG%22%20alt%3D%22Conditional%20Formatting%20Example.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20scoured%20Excel%20forums%20looking%20for%20a%20solution%20and%20haven't%20been%20able%20to%20come%20up%20with%20anything%20that%20works%20the%20way%20I%20need%20it%20to.%20I'm%20not%20new%20to%20Excel%2C%20but%20I%20don't%20have%20much%20experience%20(yet)%20with%20writing%20formulas%20with%20multiple%20conditions%2Fcriteria.%20It's%20also%20possible%20I'm%20just%20overthinking%20this.%20Any%20help%20is%20much%20appreciated!!%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2294926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2295039%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%20for%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2295039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037325%22%20target%3D%22_blank%22%3E%40VICTORIAR94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20B2%3AB200%20(or%20however%20far%20down%20the%20data%20go).%3C%2FP%3E%0A%3CP%3EB2%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Condtional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DOR(AND(C2%26lt%3B%26gt%3B%22%22%2CB2%26gt%3BC2)%2CB2%3D%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2295262%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formula%20for%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2295262%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response!%20This%20works%20in%20my%20sample%20data%20sheet%2C%20but%20does%20not%20work%20in%20my%20regular%20data%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20the%20current%20conditional%20formatting%20rules%20for%20the%20worksheet.%20I%20think%20something%20is%20interfering.%20I%20also%20included%20a%20screenshot%20of%20the%20subject%20cells%20with%20current%20conditional%20formatting%20rules.%20Not%20sure%20where%20to%20go%20from%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20the%20following%3A%3C%2FP%3E%3CP%3E-%20Block%20empty%20cells%20and%20text%20cells%20from%20any%20conditional%20formatting%3C%2FP%3E%3CP%3E-%20Highlight%20in%20red%20cells%20that%20are%20%22late%22%20%2F%20equal%20to%20or%20past%20today's%20date%3C%2FP%3E%3CP%3E-%20Highlight%20in%20yellow%20cells%20that%20are%20%22upcoming%22%20%2F%20equal%20to%20or%20within%20next%2014%20days%3C%2FP%3E%3CP%3E-%20And%20from%20my%20original%20post%2C%20highlight%20in%20orange%20cells%20with%20next%20shipment%20date%20that%20is%20after%20the%20due%20date%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CF%20Current.JPG%22%20style%3D%22width%3A%20957px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275678i54DB48A775C8986A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CF%20Current.JPG%22%20alt%3D%22CF%20Current.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAC%20is%20Next%20Shipment%20Date%3C%2FP%3E%3CP%3EIn%20the%20screenshot%20below%2C%20it%20looks%20like%20the%20orange%20formatting%20is%20overruling%20the%20%22late%22%20and%20%22upcoming%22%20formats.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CF%20Current%20Results.JPG%22%20style%3D%22width%3A%20165px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275679iAFDF1E3D11AD29B9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CF%20Current%20Results.JPG%22%20alt%3D%22CF%20Current%20Results.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

 

I am trying to write a formula for conditional formatting to be applied to two columns with dates. Column B is the Next Shipment Date, and Column C is the Due Date of the order. I have other conditional formatting to highlight in both columns if the date has passed.

 

Now, I'm trying to use conditional formatting to highlight cells in Column B if the following criteria are met:

1. If the Next Shipment Date is AFTER the Due Date

AND 2. If the Due Date is NOT blank

3. If the Next Shipment Date IS blank

 

Item 3 may need to split into its own conditional formatting rule, which is fine. However, I think items 1 & 2 need to be combined into one formula to identify the proper fields.

 

The screen shot below is from my data showing that there are rows in which the Next Shipment Date or Due Date (or both) are blank. I removed other conditional formatting from this sample.

Conditional Formatting.JPG           

The screen shot below shows what I would like to highlight based on the criteria detailed above.  

Conditional Formatting Example.JPG

 

I have scoured Excel forums looking for a solution and haven't been able to come up with anything that works the way I need it to. I'm not new to Excel, but I don't have much experience (yet) with writing formulas with multiple conditions/criteria. It's also possible I'm just overthinking this. Any help is much appreciated!! Thanks in advance!

6 Replies

@VICTORIAR94 

Select B2:B200 (or however far down the data go).

B2 should be the active cell in the selection.

On the Home tab of the ribbon, select Condtional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=OR(AND(C2<>"",B2>C2),B2="")

 

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

@Hans Vogelaar 

Thank you for your response! This works in my sample data sheet, but does not work in my regular data sheet.

 

Below is the current conditional formatting rules for the worksheet. I think something is interfering. I also included a screenshot of the subject cells with current conditional formatting rules. Not sure where to go from here.

 

I am trying to do the following:

- Block empty cells and text cells from any conditional formatting

- Highlight in red cells that are "late" / equal to or past today's date

- Highlight in yellow cells that are "upcoming" / equal to or within next 14 days

- And from my original post, highlight in orange cells with next shipment date that is after the due date

CF Current.JPG

 

AC is Next Shipment Date

In the screenshot below, it looks like the orange formatting is overruling the "late" and "upcoming" formats.

CF Current Results.JPG

@VICTORIAR94 Try moving the rule for orange down to below the rules for red and yellow.

@Hans Vogelaar 

Orange rule is now at the bottom but the highlighted cells did not change.

@VICTORIAR94 

Could you attach a small sample workbook without sensitive data that demonstrates the problem?

@Hans Vogelaar 

Sample attached below. Thank you again for your help!!