SOLVED

How to format a cell that has a date less than 30 days from another date in a different cell

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3172925%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20to%20format%20a%20cell%20that%20has%20a%20date%20less%20than%2030%20days%20from%20another%20date%20in%20a%20different%20cell%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3172925%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20would%20like%20to%20have%20a%20cell%20show%20a%20color%20if%20the%20date%20is%2030%20days%20less%20than%20another%20date%20in%20a%20different%20cell.%26nbsp%3B%20Form%20instance%2C%20if%20cell%20I33%20says%20February%2016%2C%202022%20and%20cell%20G33%20says%20Feb%2022%2C%202022%2C%20how%20to%20I%20get%20cell%20I33%20to%20show%20a%20color%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThanks%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3172925%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3173033%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20format%20a%20cell%20that%20has%20a%20date%20less%20than%2030%20days%20from%20another%20date%20in%20a%20different%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3173033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1308906%22%20target%3D%22_blank%22%3E%40Kiddo213lutz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20I33.%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'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'greater%20than'%20from%20the%20second%20drop%20down.%3C%2FP%3E%0A%3CP%3EEnter%20%3DG33-30%20in%20the%20box%20next%20to%20it.%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
Occasional Contributor

I would like to have a cell show a color if the date is 30 days less than another date in a different cell.  Form instance, if cell I33 says February 16, 2022 and cell G33 says Feb 22, 2022, how to I get cell I33 to show a color?

 

Thanks

9 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Kiddo213lutz 

Select I33.

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

Select 'Format only cells that contain'.

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

Select 'greater than' from the second drop down.

Enter =G33-30 in the box next to it.

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

@Hans Vogelaar 

How would I make this work for the whole worksheet?

@Kiddo213lutz 

Select the entire range that you want to format conditionally. The active cell should be in the first top) row of the selection. In the following, I will assume that this is row 2.

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

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

Enter the following formula in the box:

=$I2>$G2-30

Remember, 2 is the row of the active cell - change if the active cell is in another row.

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

@Hans Vogelaar 

 

2/22/20222/15/20222/17/2022

 

As an example, Feb 22, 2022 is cell I33 (expiry date) and Feb 17, 2022 is cell L33 (task end date.  I am trying to have cell L33 filled with a color if the date is too close to the expiry therefore would like it to show a color to tell me this.  I need it to show if cell L33 is less than 30 days base on the date in cell I33.  

The worksheet has a number of rows with different dates.  

@Kiddo213lutz 

Can you tell us exactly which cells you want to color? Just one column, or multiple columns?

And is row 33 the starting row of the data?

The row starts at 3 and could be as many as 70. Each row has an Expiry Date (column I) and the Task End Date (column L). I would like column L (Task End Date) to show a color if the date entered is less than 30 days of the date entered into column I (Expiry Date).

@Kiddo213lutz 

OK, so select L3:L70 or even further down if you wish.

L3 should be the active cell in 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 following formula in the box:

=AND($I3<>"",$L3>$I3-30)

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

@Hans Vogelaar 

That worked!  Thank you so much for your patience!