Conditional Format Column based on Date

[pdvsa]

Hello,

I need to color a column based on a date in a cell.
The tricky part is that the condition format condition is only dependent on the month.

Let me give an example:
Criteria:
4/18/2016

the data table looks like this:
2/1/2016 3/1/2016 4/1/2016

You can see the 4/18/2016 is not an exact match for any of the dates in the table.
I need the column 4/1/2016 to be colored.
So basically, if the month and year are a match then color it and dont pay attention to the day

I dont see how I can attach a file or else I would.
I do have a file posted here though: https://onedrive.live.com/redir?res...889&authkey=!AEE4aagNu4Ee0ok&ithint=file,xlsx
You might have to save a copy though. See the ellipses on the far right (three dots) to download it.
I have conditional formatting already on the sheet & it works only if there is an exact date match.

thank you

 

[JoeMo]

Suppose your criterion date is in A1 and your dates are in C1:E1.
select C1:E1
Home>Conditional Formatting>New>Use a formula

Formula is:
=AND(YEAR($A$1)=YEAR(C1),MONTH($A$1)=MONTH(C1))

 

[pdvsa]

Thank you JoMo. It looks good. When I get to a computer I will test it. Thank you again.

 

[pdvsa]

finally got to a computer. Looks great. Dont completely understand that formula but it works perfectly!

 

[pdvsa]

JoeMo: I need to ask a follow up that I didnt forsee when posting the question. I sometimes will not have any dates in the column and sometimes will have numbers. I need to be able to still conditionally format those cells in the column that do not have dates. Let me know what you think.

thank you

 

[JoeMo]

JoeMo: I need to ask a follow up that I didnt forsee when posting the question. I sometimes will not have any dates in the column and sometimes will have numbers. I need to be able to still conditionally format those cells in the column that do not have dates. Let me know what you think.

thank you

Can you post some sample data to show the layout and indicate the range you want to CF?

 

[pdvsa]

Joe:

please see uploaded file here:
https://onedrive.live.com/redir?res...893&authkey=!ACd02aWKX5zkPzg&ithint=file,xlsx

I shared the file but if you have an issue accessing please let me know.

 

[JoeMo]

Joe:

please see uploaded file here:
https://onedrive.live.com/redir?res...893&authkey=!ACd02aWKX5zkPzg&ithint=file,xlsx

I shared the file but if you have an issue accessing please let me know.

I can see it (barely) but you didn't describe what you want to CF. Where's the column that "sometimes will not have any dates in the column and sometimes will have numbers"? Is B1 the date you want to compare for the condition? Is it entered as a date, or is it text? .........

 

[pdvsa]

"Where's the column that "sometimes will not have any dates in the column and sometimes will have numbers"
==>You might have to scroll up to see all the data. The dates are in row 4 and is the only row that contains dates. The data below this row are numbers and row 5&6 is null. Not sure if that answers your question.

"Is B1 the date you want to compare for the condition? Is it entered as a date, or is it text?
==>Yes it is B1. It is formatted as a date and should not be text.

let me know if you have any other questions.

 

[JoeMo]

"Where's the column that "sometimes will not have any dates in the column and sometimes will have numbers"
==>You might have to scroll up to see all the data. The dates are in row 4 and is the only row that contains dates. The data below this row are numbers and row 5&6 is null. Not sure if that answers your question.

"Is B1 the date you want to compare for the condition? Is it entered as a date, or is it text?
==>Yes it is B1. It is formatted as a date and should not be text.

let me know if you have any other questions.

If you want to conditionally format the dates in row 4, select D4:CK4, use the CF formula below and select the format you want.

=AND(YEAR($B$1)=YEAR(D4),MONTH($B$1)=MONTH(D1))