[Excel 2010] Formatting calender depending on list of dates

[nusoos]

[TABLE="width: 500"]
<tbody>[TR]
[TD]dates[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]T[/TD]
[TD]F[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]01/27/2017[/TD]
[TD][/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]01/28/2017[/TD]
[TD][/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]01/29/2017[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02/02/2017[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

Hey!
All I want to do is to highlight every day in the primitive calendar on the right that is stored on the left in the dates column.
Please consider that number of dates is variable. I just can't figure out how to use conditional formatting properly here.

Big thank you in advance!

 

[nusoos]

*bump*
Some more information: Using Excel 2010 on Win10 and my only concnern is to highlight the day number of the date in the calender (calender may change).

 

[nusoos]

*bump*

 

[Joe4]

I just can't figure out how to use conditional formatting properly here.

Here is how I would probably do it.
In your primitive calendar, I would probably replace each number with an actual date.
Actually, you really only need to do the first one, and then each succeeding one would just the the previous one plus one.
Then, change the format of those dates in your primitive calendar to a Custom Format of d, so they would appear like you show.

Then, since those numbers are really dates, you could easily use Conditional Formatting like this:
First, I would highlight all the dates in the left column and name the range (something like "MyRange")
Then, highlight all the values in your primitive calendar, go to Conditional Formatting, and use this formula (if the first cell in your range is F1):

=ISNUMBER(MATCH(F1,MyDates,0))

Then choose your formatting option.

 

[clavalla]

How many side dates do you intend to have, only a couple that change as needed? If only a couple you can conditional format it.

Take your calendar and make sure its formatted as date and only displaying day. Not just adding in the number for the day. Then do a conditional format. Rule type "format only cells that contain" for rule description "Cell Value" "equal to" "cell that has date you entered on the side" then format for color or whatever you want to do with it. Repeat for number of cells you have entered for highlighting dates.

 

[nusoos]

Here is how I would probably do it.

Your solution works perfectly. I altered some specific parts so it fits the actual table, but I would've never thought of your format trick. Very nice concept and thank you very much!

 

[Joe4]

You are welcome!
Glad it all worked out for you.