SOLVED
Home

Conditonal Formatting in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-786285%22%20slang%3D%22en-US%22%3EConditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786285%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20use%20two%20colors%20for%20coloring%20dates%20that%20are%20meetings.%26nbsp%3B%20These%20dates%20are%20space%20out%20for%20the%20last%20three%20months.%20I%20want%20to%20color%20green%20for%20the%20first%20year%20and%20say%20red%20for%20the%20year%20beyond.%26nbsp%3B%20I%20have%20tried%20several%20ways%20and%20not%20getting%20the%20same%20results.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20summary%20sheet%20has%20all%20formulas%20and%20the%20conditional%20formatting%20that%20I%20tried%20did%20not%20work%20the%20same%20way.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20several%20tutorials%20and%20not%20getting%20the%20right%20answers.%3C%2FP%3E%3CP%3EP.S.%20I%20am%20using%20dates.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786285%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786315%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786315%22%20slang%3D%22en-US%22%3ESelect%20your%20date%20range%20(%20assuming%20as%20starting%20in%20A2)%20and%20apply%20this%20conditional%20formatting%20formulas%3A%3CBR%20%2F%3EFor%20green%3A%3CBR%20%2F%3E%3DA2%26lt%3B%3DEndOfFirstYear%3CBR%20%2F%3EFor%20red%3A%3CBR%20%2F%3E%3DA2%26gt%3BEndOfFirstYear%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786335%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786335%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20did%20not%20work%3C%2FP%3E%3CP%3EHere%20is%20example%22%20%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F18%2F2018%3C%2FTD%3E%3CTD%3E6%2F7%2F2017%3C%2FTD%3E%3CTD%3E6%2F7%2F2017%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F9%2F2019%3C%2FTD%3E%3CTD%3E11%2F27%2F2018%3C%2FTD%3E%3CTD%3E11%2F27%2F2018%3C%2FTD%3E%3CTD%3E11%2F29%2F2018%3C%2FTD%3E%3CTD%3E3%2F9%2F2018%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10%2F23%2F2017%3C%2FTD%3E%3CTD%3E10%2F23%2F2017%3C%2FTD%3E%3CTD%3E10%2F24%2F2017%3C%2FTD%3E%3CTD%3E10%2F24%2F2017%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%2F30%2F2018%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%2F30%2F2018%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F9%2F2019%3C%2FTD%3E%3CTD%3E2%2F1%2F2019%3C%2FTD%3E%3CTD%3E2%2F1%2F2019%3C%2FTD%3E%3CTD%3E2%2F1%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788814%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788814%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20did%20not%20work%20or%20I%20didn't%20understand%20the%20process.%20%26nbsp%3B%20Here%20is%20another%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F18%2F2018%3C%2FTD%3E%3CTD%3E6%2F7%2F2017%3C%2FTD%3E%3CTD%3E6%2F7%2F2017%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E4%2F9%2F2019%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E11%2F27%2F2018%3C%2FTD%3E%3CTD%3E11%2F27%2F2018%3C%2FTD%3E%3CTD%3E11%2F29%2F2018%3C%2FTD%3E%3CTD%3E3%2F9%2F2018%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10%2F23%2F2017%3C%2FTD%3E%3CTD%3E10%2F23%2F2017%3C%2FTD%3E%3CTD%3E10%2F24%2F2017%3C%2FTD%3E%3CTD%3E10%2F24%2F2017%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%2F30%2F2018%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%2F30%2F2018%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F9%2F2019%3C%2FTD%3E%3CTD%3E2%2F1%2F2019%3C%2FTD%3E%3CTD%3E2%2F1%2F2019%3C%2FTD%3E%3CTD%3E2%2F1%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789613%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20attach%20your%20sample%20file%20so%20that%20it%20would%20be%20easier%20to%20test%20the%20appropriate%20conditional%20formatting%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790156%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790156%22%20slang%3D%22en-US%22%3E%3CP%3EAttach%20is%20sample%20of%20what%20I%20am%20working%20with.%20I%20want%20to%20highlight%20less%20than%20year%20and%20red%20over%20a%20year%20old.%3C%2FP%3E%3CP%3EThank%20You%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790906%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3D(A3%26gt%3BEDATE(TODAY()%2C-12))*ISNUMBER(A3)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20the%20green%20and%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3D(A3%26lt%3B%3DEDATE(TODAY()%2C-12))*(A3%26gt%3B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20the%20red%2C%20applied%26nbsp%3B%20to%26nbsp%3B%3D%24A%243%3A%24I%248%3C%2FP%3E%3C%2FLINGO-BODY%3E
frontdesksm
Occasional Contributor

I would like to use two colors for coloring dates that are meetings.  These dates are space out for the last three months. I want to color green for the first year and say red for the year beyond.  I have tried several ways and not getting the same results.  

 

My summary sheet has all formulas and the conditional formatting that I tried did not work the same way.  

 

I have tried several tutorials and not getting the right answers.

P.S. I am using dates. 

 

6 Replies
Select your date range ( assuming as starting in A2) and apply this conditional formatting formulas:
For green:
=A2<=EndOfFirstYear
For red:
=A2>EndOfFirstYear

It did not work

Here is example"  

ABCDEFH
10/18/20186/7/20176/7/2017    
4/9/201911/27/201811/27/201811/29/20183/9/2018  
 10/23/201710/23/201710/24/201710/24/2017  
 8/30/2018 8/30/2018   
       
4/9/20192/1/20192/1/20192/1/2019   

@frontdesksm 

This did not work or I didn't understand the process.   Here is another @Twifoo 

ABCDEF  
        
10/18/20186/7/20176/7/2017     
4/9/201911/27/201811/27/201811/29/20183/9/2018   
 10/23/201710/23/201710/24/201710/24/2017   
 8/30/2018 8/30/2018    
        
4/9/20192/1/20192/1/20192/1/2019    

@frontdesksm 

Please attach your sample file so that it would be easier to test the appropriate conditional formatting formula.

Attach is sample of what I am working with. I want to highlight less than year and red over a year old.

Thank You for your help.

 

 

@Twifoo 

Highlighted
Solution

@frontdesksm 

Perhaps

=(A3>EDATE(TODAY(),-12))*ISNUMBER(A3)

for the green and

=(A3<=EDATE(TODAY(),-12))*(A3>1)

for the red, applied  to =$A$3:$I$8

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies