Conditional format date if it matches two conditions

cpereznj

New Member
Joined
Nov 27, 2012
Messages
19
I have searched the web for a solution to my question with no luck. Here is my issue:
I have cell A1 with a date (any date), I also have two named ranges. One named Holidays and the other one named Payroll. They are used to identify the company's holidays and the payroll dates.
I need to conditional format cell A1 if the date matches a date in the Holidays range and a date in the Payroll range. Tried unsuccessfully the following formula: =AND(A1=Holidays,A1=Payroll). I have used =OR(A1=Holidays) plus =OR(A1=Payroll). Both work properly. However, I need it to work if both conditions are met not separately. Any help will be appreciated.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Try
=AND(OR(A1=Holidays),OR(A1=payroll))
 

jjasmith4

New Member
Joined
Aug 22, 2018
Messages
48
Holidays and Payroll have more than one date, right? So just A1=Holidays or A1=Payroll fail.

Try this:
=NOT(ISNA(MATCH(A1,Holidays,0)+MATCH(A1,Payroll,0)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top