Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 13th 16, 08:16 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 94
Default 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

  #2   Report Post  
Old March 13th 16, 09:42 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default 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
  #3   Report Post  
Old March 13th 16, 10:05 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 94
Default 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

  #4   Report Post  
Old March 13th 16, 10:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default 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
  #5   Report Post  
Old March 13th 16, 02:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 94
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating dates to include Saturdays but not Sundays Rebecca[_5_] Excel Discussion (Misc queries) 1 January 26th 10 08:39 PM
No. of Saturdays between 2 dates Bhupinder Rayat Excel Worksheet Functions 5 January 14th 08 06:27 PM
Difference - except for Saturdays and Sundays Rick[_3_] Excel Worksheet Functions 10 December 29th 07 01:51 PM
Calculate the number of Saturdays or Sundays between 2 dates? Jim Long Excel Discussion (Misc queries) 1 November 1st 05 07:13 PM
Need number of Saturdays and number of Sundays between 2 dates Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017