Conditional Formatting Help on Calendar

**Nevermore97** · 06-17-2014, 03:43 AM

Hello All,

I have prepared a calendar specifying and highlighting dates in the calendar using conditional formatting.

The issue I am facing (and I have been trying to resolve the same for past three hours to no luck), is that when two events appear on same date, I'm not able to incorporate the effect of it. Sheet 2 in the attachment represents the color scheme and calendar events which I wanted to include, whereas Main Sheet represents the Calendar itself.

Would really really appreciate if someone could suggest anyway to resolve it by having a look at the file (only in January, I'll copy the formulae for other months).

Thanksss in Advance!

**Nevermore97** · 06-17-2014, 08:51 AM

Please could anyone help me out with it. Please

**ben_hensel** · 06-17-2014, 11:11 AM

My first instinct is that Excel is fundmanentally the wrong tool for this.

What is your email program? If it's Outlook, then I would push you towards using the built-in calendar tools there; ditto other clients (Novell Groupwise or Thunderbird+Lightning for example) with built-in calendar functions with auto-recurrance.

But if it's gotta be in excel (like this has to be sharable with people on different enterprise software?)....

I gues my advice for this spreadsheet is: simplify.

You've got like 400 Conditional Formatting rules applied to all sorts of ranges for 10 different outcomes listed in Sheet1.

So come up with 10 rules; figure out an equation (probly using the DAY and WEEKDAY functions) to see if they apply, and then apply them to the entire area of the calendar.

I would probably also push you to a linearized calender in another tab that you use as an "event master" that has the date marching down day-by-day in one column, a list of all possible events along the top, and then everything can be TRUE/FALSE in a table. Rightmost column is a summary that returns the "events code" listing what happens that day. Then you can just run a lookup on the "events code" for your conditional formatting...?

Something like that.

**Nevermore97** · 06-18-2014, 01:50 AM

Thank you Ben for the reply.

Actually, my boss wanted it to be in Excel format. So, even though it is much better to do in SharePoint / Outlook, where it could be synchronized with calendars of different members of finance team, I have no option but to proceed with the calendar in excel format.

Isn't there any formula you could think so that I can apply conditional formatting on events falling on same date. I already showed my boss this format and he has reviewed and approved it, if I'll change it again, he's definitely going to be pissed off