ExcelBanter - FINDING OUT SATURDAYS AND SUNDAYS

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - FINDING OUT SATURDAYS AND SUNDAYS (https://www.excelbanter.com/excel-worksheet-functions/451343-finding-out-saturdays-sundays.html)

FINDING OUT SATURDAYS AND SUNDAYS

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

Please help.

-via135

FINDING OUT SATURDAYS AND SUNDAYS

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

FINDING OUT SATURDAYS AND SUNDAYS

On Sunday, 13 March 2016 00:16:07 UTC-8, via135 wrote:
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

Please help.

-via135

Thanks for responding. However, I am not getting any of the formats using the rules. Please advise.

-via135

FINDING OUT SATURDAYS AND SUNDAYS

Hi,

Am Sun, 13 Mar 2016 03:05:46 -0700 (PDT) schrieb via135:

Thanks for responding. However, I am not getting any of the formats using the rules. Please advise.

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for CFWeekdays

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

FINDING OUT SATURDAYS AND SUNDAYS

On Sunday, 13 March 2016 03:14:36 UTC-7, Claus Busch wrote:
Hi,

Am Sun, 13 Mar 2016 03:05:46 -0700 (PDT) schrieb via135:

Thanks for responding. However, I am not getting any of the formats using the rules. Please advise.

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for CFWeekdays

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

Yes..It works.!!!
Mistake is mine since I had selected only Col A.!

Thanks for the help and sharing.!!!

-via135