Conditional Formatting Formulas for Multiple Dates

**smart_as** · 03-01-2011, 02:05 PM

This is my very post so apologies in advance if I leave out any details needed. I am using Conditional Formatting in Excel 2010 and trying to create a calendar which I can enter my key financial dates and it will colour code the relevant dates on the calendar. I am really struggling with the formula required to highlight the 22nd of each month in the same colour and preferably the last working day before if the 22nd is a weekend.

I have tried various different formulae today with no success. Am I taking the best approach or should I be looking at VBA? Please help I do not have much hair and even less after today

I have attached the workbook I have prepared and the dates that need to be displayed on the calendar are in AD10:AD21 although these do not necessarily need to be used if another way can be found to reach the same answer.

**ChemistB** · 03-01-2011, 02:57 PM

Select A20:W52 and In conditional formatting

=IF(WEEKDAY(A20,2)=5,OR(DAY(A20)=20,DAY(A20)=21),IF(WEEKDAY(A20,2)<6,DAY(A20)=22))
Format appropriately
Does that work for you?

**smart_as** · 03-02-2011, 02:38 AM

Hey ChemistB that works to a degree. It does not currently conditional format April cell N23 or July cell N32 in the same way. I have formatted the cells in the same manner as the others. If you amend the date selection for the calendar you will see that this issue is not consistent to any one date block it will move around the calendar.

Please advise. Your help is greatly appreciated.

**ChemistB** · 03-02-2011, 10:05 AM

oops, missed the option of it being friday and the date being the 22nd

=IF(WEEKDAY(A20,2)=5,OR(DAY(A20)=20,DAY(A20)=21, Day(A20)=22),IF(WEEKDAY(A20,2)<6,DAY(A20)=22))

How's that work?