Forum Discussion
How to format a cell that has a date less than 30 days from another date in a different cell
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.
How would I make this work for the whole worksheet?
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.
2/22/2022 2/15/2022 2/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.
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).