May 04 2021 05:34 AM
In an excel table, I have column "A" which contains folder names.
Column "B" contains the date when the file must be completed.
I would like :
- the cell in column A is on a GREEN background if today's date is lower than the date in column "B" minus 15 days (eg: if my deadline date is 05/30/2021 my cell column A will be green until today's date reaches 05/15/2021)
If the time between today and the date in column "B" is between 15 and 0 days: I would like the cell in column "A" to be on a YELLOW background.
If today's date has passed the date in column "B": I would like the cell in column "A" to have a RED background.
Thanks in advance for any help and sorry for my English who's not perfect !
May 04 2021 06:05 AM
Select the folder names in column A. I will assume that A2 is 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 formula =$B2>0
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
Repeat the above steps, but with the formula =$B2>=TODAY() and yellow as fill color.
And repeat them again with the formula =$B2>TODAY()+15 and green as fill color.
May 05 2021 02:48 AM
May 05 2021 02:55 AM
@Lionreaux You were supposed to select the entire range with folder names, and then create the conditional formatting rules. That way the rules will apply to all selected cells.
May 05 2021 12:24 PM
@Hans Vogelaar, hmm this isnt working when i select the entire range and i dont understand why...
Sorry my excel is in french so maybe the problem is the way that i write my dates?
May 05 2021 01:01 PM
SolutionIn French, the TODAY() function is named AUJOURDHUI()
So use
=$B2>AUJOURDHUI()+15
and
=$B2>=AUJOURDHUI()
May 05 2021 01:01 PM
SolutionIn French, the TODAY() function is named AUJOURDHUI()
So use
=$B2>AUJOURDHUI()+15
and
=$B2>=AUJOURDHUI()