SOLVED

put color date

Copper Contributor
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 !
6 Replies

@Lionreaux 

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.

 

S0370.png

This is working with one cell but did i have to do that with all the cells?

@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.

@Hans Vogelaar, hmm this isnt working when i select the entire range and i dont understand why...

Lionreaux_0-1620242544537.png

 

Lionreaux_1-1620242591992.png

Sorry my excel is in french so maybe the problem is the way that i write my dates?

best response confirmed by allyreckerman (Microsoft)
Solution

@Lionreaux 

In French, the TODAY() function is named AUJOURDHUI()

So use

 

=$B2>AUJOURDHUI()+15

 

and

 

=$B2>=AUJOURDHUI()

Thanks a lot, it works !
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Lionreaux 

In French, the TODAY() function is named AUJOURDHUI()

So use

 

=$B2>AUJOURDHUI()+15

 

and

 

=$B2>=AUJOURDHUI()

View solution in original post