To find Friday, Saturday

[Waqas ali]

hi,
dear all,
how i can find only friday and saturday dates between two date range
for example
form 22-feb-11 to 02-mar-11
how i can find in which date is friday and saturday.

thanks for your cooperation

 

[edokhotnik]

hi,
dear all,
how i can find only friday and saturday dates between two date range
for example
form 22-feb-11 to 02-mar-11
how i can find in which date is friday and saturday.

thanks for your cooperation

With the dates in Column A and Column B formatted with custom date dddd -- the following will give you the days of the week.

Excel Workbook
	A	B
2	2/22/2011	Tuesday
3	2/23/2011	Wednesday
4	2/24/2011	Thursday
5	2/25/2011	Friday
6	2/26/2011	Saturday
7	2/27/2011	Sunday
8	2/28/2011	Monday
9	3/1/2011	Tuesday
10	3/2/2011	Wednesday
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=WEEKDAY(A2,1)
B3	=WEEKDAY(A3,1)
B4	=WEEKDAY(A4,1)
B5	=WEEKDAY(A5,1)
B6	=WEEKDAY(A6,1)
B7	=WEEKDAY(A7,1)
B8	=WEEKDAY(A8,1)
B9	=WEEKDAY(A9,1)
B10	=WEEKDAY(A10,1)

 

[Dave Patton]

Excel Workbook
	A	B	C
1	Date	Week day	Fri or Sat
2	Tue 22-Feb-11	Tuesday	FALSE
3	Wed 23-Feb-11	Wednesday	FALSE
4	Thu 24-Feb-11	Thursday	FALSE
5	Fri 25-Feb-11	Friday	TRUE
6	Sat 26-Feb-11	Saturday	TRUE
7	Sun 27-Feb-11	Sunday	FALSE
8	Mon 28-Feb-11	Monday	FALSE
9	Tue 01-Mar-11	Tuesday	FALSE
10	Wed 02-Mar-11	Wednesday	FALSE
1d

Excel 2003

Cell Formulas
Range	Formula
B2	=TEXT(A2,"dddd")
C2	=WEEKDAY(A2,1)>5

Custom format the dates as ddd dd-mmm-yy or dddd dd-mmm-yy or
Formula
Check Conditional formatting

 

[Waqas ali]

thanks,
i want get only date of friday and saturday.
i have two date field from and to only with in two dates formula give me automatically friday and saturday dates in other two cell.
no need put dates one by one in each cell.

 

[Haseeb Avarakkan]

Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Start</td><td style="font-weight: bold;;">End</td><td style="font-weight: bold;;">Total Fri</td><td style="font-weight: bold;;">Total Sat</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Friday</td><td style="font-weight: bold;;">Saturday</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">9-Feb-11</td><td style="text-align: right;;">2-Mar-11</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">11-Feb-11</td><td style="text-align: right;;">12-Feb-11</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">18-Feb-11</td><td style="text-align: right;;">19-Feb-11</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25-Feb-11</td><td style="text-align: right;;">26-Feb-11</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">ROW(<font color="Purple">INDEX(<font color="Teal">A:A,A2</font>):INDEX(<font color="Teal">A:A,B2</font>)</font>)</font>)=6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">ROW(<font color="Purple">INDEX(<font color="Teal">A:A,A2</font>):INDEX(<font color="Teal">A:A,B2</font>)</font>)</font>)=7</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">B2<(<font color="Red">A2+CHOOSE(<font color="Green">WEEKDAY(<font color="Purple">A2</font>),5,4,3,2,1,0,6</font>)</font>),"",A2+CHOOSE(<font color="Red">WEEKDAY(<font color="Green">A2</font>),5,4,3,2,1,0,6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">F2="","",IF(<font color="Red">F2+1>$B$2,"",F2+1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=IF(<font color="Blue">F2="","",IF(<font color="Red">F2+7<$B$2,F2+7,""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down... F3 & G2 formulas.

 

[p45cal]

Excel Workbook
	L	M	N
11	22-Feb-11
12	02-Mar-11
13			Fri 25/02/2011
14			Sat 26/02/2011
Sheet

If there are more than two Fridays and Saturdays inbetween the dates it will only show the first two.