Conditional Formatting Dynamic Calendar

**soubriquet** · 06-24-2013, 08:50 AM

So I've set up a continuous calendar using the famous array formula :

=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))<>MONTH(DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

I'm now trying to use conditional formatting within that array to highlight certain dates, such as the last and second last friday of the month. I have the formulas on their own, which work, but when I apply them through conditional formatting, I get some wonky results. I'm sort of new to conditional formatting, so any help is much appreciated.

**akhileshgs** · 06-24-2013, 09:33 AM

cab you pls upload the sample file u have been working?

**soubriquet** · 06-24-2013, 02:14 PM

Uploaded! Thanks for the reply.

I've managed to sort out the conditional formatting. Problem now is I need to find a way to calculate backward from the end of the month to find the third last friday! Any help would be appreciated.