SOLVED

put color date

%3CLINGO-SUB%20id%3D%22lingo-sub-2321547%22%20slang%3D%22en-US%22%3Eput%20color%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321547%22%20slang%3D%22en-US%22%3E%3CPRE%3E%3CSPAN%20class%3D%22Y2IQFc%22%3EIn%20an%20excel%20table%2C%20I%20have%20column%20%22A%22%20which%20contains%20folder%20names.%0AColumn%20%22B%22%20contains%20the%20date%20when%20the%20file%20must%20be%20completed.%0A%20%0AI%20would%20like%20%3A%0A-%20the%20cell%20in%20column%20A%20is%20on%20a%20GREEN%20background%20if%20today's%20date%20is%20lower%20than%20the%20date%20in%20column%20%22B%22%20minus%2015%20days%20(eg%3A%20if%20my%20deadline%20date%20is%2005%2F30%2F2021%20my%20cell%20column%20A%20will%20be%20green%20until%20today's%20date%20reaches%2005%2F15%2F2021)%0A%20%0AIf%20the%20time%20between%20today%20and%20the%20date%20in%20column%20%22B%22%20is%20between%2015%20and%200%20days%3A%20I%20would%20like%20the%20cell%20in%20column%20%22A%22%20to%20be%20on%20a%20YELLOW%20background.%0AIf%20today's%20date%20has%20passed%20the%20date%20in%20column%20%22B%22%3A%20I%20would%20like%20the%20cell%20in%20column%20%22A%22%20to%20have%20a%20RED%20background.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%20for%20any%20help%20and%20sorry%20for%20my%20English%20who's%20not%20perfect%20!%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2321547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2325471%22%20slang%3D%22fr-FR%22%3ERe%3A%20put%20color%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2325471%22%20slang%3D%22fr-FR%22%3EThis%20is%20working%20with%20one%20cell%20but%20did%20I%20have%20to%20do%20that%20with%20all%20the%20cells%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2325502%22%20slang%3D%22en-US%22%3ERe%3A%20put%20color%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2325502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044630%22%20target%3D%22_blank%22%3E%40Lionreaux%3C%2FA%3E%20You%20were%20supposed%20to%20select%20the%20entire%20range%20with%20folder%20names%2C%20and%20then%20create%20the%20conditional%20formatting%20rules.%20That%20way%20the%20rules%20will%20apply%20to%20all%20selected%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2321632%22%20slang%3D%22en-US%22%3ERe%3A%20put%20color%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044630%22%20target%3D%22_blank%22%3E%40Lionreaux%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20folder%20names%20in%20column%20A.%20I%20will%20assume%20that%20A2%20is%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon.%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%26nbsp%3B%20%3D%24B2%26gt%3B0%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20red.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20the%20above%20steps%2C%20but%20with%20the%20formula%20%3D%24B2%26gt%3B%3DTODAY()%20and%20yellow%20as%20fill%20color.%3C%2FP%3E%0A%3CP%3EAnd%20repeat%20them%20again%20with%20the%20formula%20%3D%24B2%26gt%3BTODAY()%2B15%20and%20green%20as%20fill%20color.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0370.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277841iF359FEE3E5C384E0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0370.png%22%20alt%3D%22S0370.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 !