• 508K Members
• 4,111 Online
• 605K Conversations
SOLVED

## Conditonal Formatting in Excel

Occasional Contributor

# Conditonal Formatting in Excel

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

# Re: Conditonal Formatting in Excel

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

# Re: Conditonal Formatting in Excel

It did not work

Here is example"

 A B C D E F H 10/18/2018 6/7/2017 6/7/2017 4/9/2019 11/27/2018 11/27/2018 11/29/2018 3/9/2018 10/23/2017 10/23/2017 10/24/2017 10/24/2017 8/30/2018 8/30/2018 4/9/2019 2/1/2019 2/1/2019 2/1/2019
Highlighted

# Re: Conditonal Formatting in Excel

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

 A B C D E F 10/18/2018 6/7/2017 6/7/2017 4/9/2019 11/27/2018 11/27/2018 11/29/2018 3/9/2018 10/23/2017 10/23/2017 10/24/2017 10/24/2017 8/30/2018 8/30/2018 4/9/2019 2/1/2019 2/1/2019 2/1/2019

# Re: Conditonal Formatting in Excel

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

# Re: Conditonal Formatting in Excel

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

Solution

# Re: Conditonal Formatting in Excel

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
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies
Adding Display Zero as Dashes to Custom Excel Number Format
emmagreen in Excel on
1 Replies
Relative conditional formatting
melissach in Excel on
8 Replies