[Excel 2010] Formatting calender depending on list of dates

nusoos

New Member
Joined
Dec 19, 2016
Messages
4
datesMTWTFSS
01/27/201716171819202122
01/28/201723242526272829
01/29/2017303112345
02/02/20176789101112

<tbody>
</tbody>

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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

nusoos

New Member
Joined
Dec 19, 2016
Messages
4
*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).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
=ISNUMBER(MATCH(F1,MyDates,0))
Then choose your formatting option.
 

clavalla

New Member
Joined
Dec 21, 2016
Messages
3

ADVERTISEMENT

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

New Member
Joined
Dec 19, 2016
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad it all worked out for you.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top