Conditional formating is applied based on date in two cells [Solved/Closed]

Report
Posts
1
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 23, 2014
-
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
-
Hi everyone
I'm using Excel 2010 and applying conditional formating on dates in Column E. I don't want the formatting to be applied if there is the following wording used in the cells in column H.
Eligible, Ineligible, Early Intervention, Withdrawn. In other words once a drop down list is used in a cell in Column H to enter the required text I want the current formatting applied to the cell in column E to be removed.
The current rules that I have are:
Cell Value = Today() - format cell red
Cell Value = Today()+1 - format cell orange
Cell Value = Today()+2 - format cell yellow
Cell Value < Today() - format cell text red

I would like all the formatting to be removed once the drop downlist is used in column H to enter one of the above four values. The default state of the cell in column H is a blank cell.
Thanking you in advance.

4 replies

Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
Hi Capricorn,

What you are looking for is the AND function, which lets you enter more conditions.

=AND($E2=TODAY(),$H2="")

or more specifically:

=AND($E2=TODAY(),$H2<>"Eligible",$H2<>"Ineligible",$H2<>"Early Intervention",$H2<>"Withdrawn")


Best regards,
Trowa

Hi TrowaD
Thank you for your patience. I took the file home on the weekend and finally got it work. Thank you very much.
Regards
Capricorn56
Hi Kioskea
Thank you for your reply and sorry it has taken me so long to respond. I've had some other technical issues. I have tried the formula and it doesn't work. Do you have any other suggestions I could try. Thanking you for your time.
Kind Regards
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
Hi Capricorn,

Actually the formula works.
I put today's date in E1:E3, Eligible in H1 and Withdrawn in H3.
As a result only the date in E2 got formatted as seen in the below pic:


It is a shorter version with less conditions but it does the trick for the example shown.
It is a Dutch version where EN means AND, VANDAAG means TODAY and I have to use ; as separator instead of ,.

If you can't figure out what is happening then consider posting your workbook using a file sharing site like www.speedyshare.com or ge.tt and post back the download link. Careful with personal information.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Hi,

I want a cell to change colour when a date that is in the cell is within 1 month of today.
Can you help with that.
I know that there is a function to do it but I am having issues and NEED HELP...
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
Hi Keno,

Here is your requested formula:
=AND(A1<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())),A1>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))

You might want to change the < and > symbols into <= and >= depending on the results you are after.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!