Hi,

Am Sun, 13 Mar 2016 00:16:00 -0800 (PST) schrieb via135:

How Can I create a rule in conditional formatting for highlighting all second saturdays in one clolur,fourth saturdays in another colour and all the sundays in another color in a list of dates in Col A & days in Col B.

date day
3/1/2016 Tue
3/2/2016 Wed
3/3/2016 Thu
3/4/2016 Fri
3/5/2016 Sat
3/6/2016 Sun
3/7/2016 Mon
3/8/2016 Tue
3/9/2016 Wed
3/10/2016 Thu
3/11/2016 Fri
3/12/2016 Sat
3/13/2016 Sun
3/14/2016 Mon
3/15/2016 Tue
3/16/2016 Wed
3/17/2016 Thu
3/18/2016 Fri
3/19/2016 Sat
3/20/2016 Sun
3/21/2016 Mon
3/22/2016 Tue
3/23/2016 Wed
3/24/2016 Thu
3/25/2016 Fri
3/26/2016 Sat
3/27/2016 Sun
3/28/2016 Mon
3/29/2016 Tue
3/30/2016 Wed
3/31/2016 Thu
4/1/2016 Fri
4/2/2016 Sat
4/3/2016 Sun
4/4/2016 Mon
4/5/2016 Tue
4/6/2016 Wed
4/7/2016 Thu
4/8/2016 Fri
4/9/2016 Sat
4/10/2016 Sun
4/11/2016 Mon
4/12/2016 Tue
4/13/2016 Wed
4/14/2016 Thu
4/15/2016 Fri
4/16/2016 Sat
4/17/2016 Sun
4/18/2016 Mon
4/19/2016 Tue
4/20/2016 Wed
4/21/2016 Thu
4/22/2016 Fri
4/23/2016 Sat
4/24/2016 Sun
4/25/2016 Mon
4/26/2016 Tue
4/27/2016 Wed
4/28/2016 Thu
4/29/2016 Fri
4/30/2016 Sat

Rule1(every 4th Saturday):
=IF(WEEKDAY($A2)<7,0,MOD(SUMPRODUCT(N(WEEKDAY($A$ 2:$A2)=7)),4)=0)
Rule2(every 2nd Saturday):
=IF(WEEKDAY($A2)<7,0,MOD(SUMPRODUCT(N(WEEKDAY($A$ 2:$A2)=7)),2)=0)
Rule3(every Sunday):
=WEEKDAY($A2)=1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional