Home

Conditional formatting assistance please

%3CLINGO-SUB%20id%3D%22lingo-sub-1178235%22%20slang%3D%22en-US%22%3EConditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178235%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20highlight%20a%20cell%20yellow%20if%20the%20date%20is%2030%20days%20out%3F%3C%2FP%3E%3CP%3EHow%20do%20I%20highlight%20a%20cell%20red%20if%20the%20date%20is%20today%20or%20passed%3F%3C%2FP%3E%3CP%3EHow%20do%20I%20leave%20a%20cell%20alone%20if%20there%20is%20no%20date%20in%20the%20cell%3F%3C%2FP%3E%3CP%3EAll%203%20rules%20would%20be%20for%201%20cell.%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1178235%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178318%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178318%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F561109%22%20target%3D%22_blank%22%3E%40c_frndz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20with%20formulas%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Ered%0A%3DA1%26gt%3B%3DTODAY()%0A%0Ayellow%0A%3D(TODAY()-A1)%26lt%3B30%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ethe%20rest%20won't%20be%20formatted%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178388%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20Sergei.%20Please%20see%20attached.%20It%20doesn't%20seem%20like%20the%20red%20formula%20worked%20unless%20i%20did%20it%20wrong.%20Please%20advise.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Red.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171554i50F80FE21E1E7784%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Red.png%22%20alt%3D%22Red.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yellow.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171555i7225CE25EA1A0535%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Yellow.png%22%20alt%3D%22Yellow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178405%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F561109%22%20target%3D%22_blank%22%3E%40c_frndz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20it%20shall%20be%20the%20opposite%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTODAY()%26gt%3B%3D%24G%2419%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20check%20if%20the%20date%20in%20the%20cell%20is%20in%20the%20past%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178434%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178434%22%20slang%3D%22en-US%22%3EThank%20you%20again.%20However%2C%20now%20the%20cell%20will%20remain%20remain%20even%20with%20no%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178866%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F561109%22%20target%3D%22_blank%22%3E%40c_frndz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EForgot%20about%20blanks%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(TODAY()%26gt%3B%3D%24G%2419)*(%24G%2419%26gt%3B1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179925%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179925%22%20slang%3D%22en-US%22%3ESergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3ENot%20sure%20what%20going%20on%20as%20I%20entered%20the%20formula%20as%20you%20have%20it.%20However%2C%20when%20I%20enter%20a%20date%20in%20the%20cell%20like%201%2F30%2F20%20it%20highlights%20the%20cell%20yellow%20rather%20than%20red.%20And%20when%20I%20enter%203%2F30%2F20%20it%E2%80%99s%20still%20yellow.%20Thanks%20again%20for%20your%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1180213%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1180213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F561109%22%20target%3D%22_blank%22%3E%40c_frndz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20shall%20be%20as%20here%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20117px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171822iF12CC0C1C28EFD60%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20CF%20rules%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183010%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20assistance%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20thank%20you%20for%20all%20your%20help%20and%20apologize%20as%20I'm%20a%20little%20confused.%20So%20basically%20i%20want%20a%20cell%20to%20turn%20yellow%20when%20it%20is%2030%20days%20or%20less%20from%20the%20expiration%20date.%20Turn%20red%20once%20it%20reaches%20or%20surpassses%20the%20the%20expiration%20date.%20Have%20no%20color%20when%20there%20is%20no%20data%20in%20the%20cell.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

How do I highlight a cell yellow if the date is 30 days out?

How do I highlight a cell red if the date is today or passed?

How do I leave a cell alone if there is no date in the cell?

All 3 rules would be for 1 cell.

Thanks in advance!

8 Replies
Highlighted

@c_frndz 

You may use with formulas

red
=A1>=TODAY()

yellow
=(TODAY()-A1)<30

the rest won't be formatted

Highlighted

@Sergei Baklan Thank you Sergei. Please see attached. It doesn't seem like the red formula worked unless i did it wrong. Please advise.

Red.png

Yellow.png

  

Highlighted

@c_frndz 

Sorry, it shall be the opposite

=TODAY()>=$G$19

to check if the date in the cell is in the past

Highlighted
Thank you again. However, now the cell will remain remain even with no data.
Highlighted

@c_frndz 

Forgot about blanks

=(TODAY()>=$G$19)*($G$19>1)
Highlighted
Sergei,

Not sure what going on as I entered the formula as you have it. However, when I enter a date in the cell like 1/30/20 it highlights the cell yellow rather than red. And when I enter 3/30/20 it’s still yellow. Thanks again for your time.
Highlighted

@c_frndz 

Is it shall be as here?

image.png

Please check CF rules in attached file

Highlighted

@Sergei Baklan I thank you for all your help and apologize as I'm a little confused. So basically i want a cell to turn yellow when it is 30 days or less from the expiration date. Turn red once it reaches or surpassses the the expiration date. Have no color when there is no data in the cell.  

Related Conversations
Documentation Migration
SunLeo in Office 365 on
0 Replies
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Relative conditional formatting
melissach in Excel on
8 Replies