Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2480337%22%20slang%3D%22en-US%22%3EConditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2480337%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20highlight%20the%20date%20in%20a%20column%20if%20that%20date%20is%20greater%20than%2030%20days%20from%20a%20date%20in%20another%20column%2C%20but%20I%20cant%20seem%20to%20find%20the%20right%20formula.%26nbsp%3B%20It%20should%20be%20fairly%20simple%2C%20but%20I%20cant%20seem%20to%20get%20it.%26nbsp%3B%20Any%20assistance%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2480337%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-2480430%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2480430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1086975%22%20target%3D%22_blank%22%3E%40justmedjn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20dates%20that%20you%20want%20to%20format%20conditionally%20are%20in%20D2%3AD100%2C%20and%20the%20other%20dates%20in%20F2%3AF100.%3C%2FP%3E%0A%3CP%3ESelect%20D2%3AD100.%20I%20will%20assume%20that%20D2%20is%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20drop%20down%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'less%20than'%20from%20the%20second%20drop%20down.%20(You%20may%20have%20to%20select%20'greater%20than'%20instead%20-%20you'll%20have%20to%20experiment).%3C%2FP%3E%0A%3CP%3EIn%20the%20box%20next%20to%20it%2C%20enter%20the%20formula%20%3DF2-30.%20(You%20might%20need%20%3DF2%2B30%20-%20again%2C%20experiment).%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
Occasional Visitor

I would like to highlight the date in a column if that date is greater than 30 days from a date in another column, but I cant seem to find the right formula.  It should be fairly simple, but I cant seem to get it.  Any assistance would be greatly appreciated!

1 Reply

@justmedjn 

Let's say the dates that you want to format conditionally are in D2:D100, and the other dates in F2:F100.

Select D2:D100. I will assume that D2 is the active cell in the selection.

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

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'less than' from the second drop down. (You may have to select 'greater than' instead - you'll have to experiment).

In the box next to it, enter the formula =F2-30. (You might need =F2+30 - again, experiment).

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.