SOLVED

Need formula for Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1732393%22%20slang%3D%22en-US%22%3ENeed%20formula%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1732393%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20in%20need%20of%20a%20formula%20for%20conditional%20formatting%20that%20can%20fill%20in%20a%20cell%20in%20column%20U%20or%20N%20when%20there%20is%20no%20date%20and%20the%20corresponding%20date%20in%20M%20or%20N%20has%20past.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20for%20conditional%20formatting%20that%20can%20identify%20cells%20in%20columns%20U%20or%20V%20(without%20dates%20in%20them)%20that%20are%20overdue%20as%20specified%20in%20Columns%20M%20or%20N.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-09-30_14-41-40.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223155i2BD379B134AE0A6A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-09-30_14-41-40.jpg%22%20alt%3D%222020-09-30_14-41-40.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1732393%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-1732485%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1732485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F595296%22%20target%3D%22_blank%22%3E%40Jon_Hurtado%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20U4%3AV1000%20(or%20however%20far%20down%20the%20data%20go).%20I%20will%20assume%20that%20U4%20is%20the%20active%20cell%20within%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%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%3DAND(U4%3D%22%22%2CM4%3CTODAY%3E%0A%3C%2FTODAY%3E%3C%2FP%3E%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%20highlight%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-1735143%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F595296%22%20target%3D%22_blank%22%3E%40Jon_Hurtado%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(U4%3D%22-%22%2CM4%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am in need of a formula for conditional formatting that can fill in a cell in column U or N when there is no date and the corresponding date in M or N has past.  

I need a formula for conditional formatting that can identify cells in columns U or V (without dates in them) that are overdue as specified in Columns M or N.  

 

 

2020-09-30_14-41-40.jpg

 

 

 

4 Replies
Highlighted

@Jon_Hurtado 

Select U4:V1000 (or however far down the data go). I will assume that U4 is the active cell within the selection.

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

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

Enter the formula

 

=AND(U4="",M4<TODAY())

 

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

Highlighted

@Hans Vogelaar 

 

Thanks for responding, but that did not work.

Highlighted
Best Response confirmed by Jon_Hurtado (Occasional Contributor)
Solution

@Jon_Hurtado 

How about

 

=AND(U4="-",M4<TODAY())

Highlighted

@Hans Vogelaar

 

Perfect!  Many thanks.