Finding Saturday dates

[Eva Shanley]

Hello,
If A2 contains 3/28/2004 and B2 contains 5/1/2004, is
there a way to return all the Saturday dates that would be
found between and including those 2 dates? Thanks for
your help!

 

[Soo Cheon Jheong]

Hi,


=IF(N(IF(B2<A2+7-WEEKDAY(A2),"-",A2+7-WEEKDAY(A2)))=0,0,
INT((IF(A2>(B2-IF(WEEKDAY(B2)<7,WEEKDAY(B2))),"-",
B2-IF(WEEKDAY(B2)<7,WEEKDAY(B2)))-IF(B2<A2+7-WEEKDAY(A2),
"-",A2+7-WEEKDAY(A2)))/7)+1)


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Frank Kabel]

Hi
and how do you want the output?

 

[Jason Morin]

You could put this in A3, press ctrl/shift/enter, and
fill down:

=SMALL(IF(WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2)))=7,ROW
(INDIRECT($A$2&":"&$B$2))),ROW()-2)

It'll error out when it exceeds the date in B2. Fill this
formula down as far you need. If the time period between
A2 and B2 will be no more than 1 year, for example, copy
down to about row 56 (52 Saturdays - a few rows).

Format these cells as date under Format > Cells.

HTH
Jason
Atlanta, GA

 

[Eva Shanley]

Hi Frank,
Formatted as 3/3/2004, 3/10/2004 if I have a choice! That
would be ever so nice. TIA.

 

[Frank Kabel]

Hi
see Jason's solution

 

[Eva Shanley]

Formula works great; thanks! If I have a begin and end
date for all 12 months in rows, can I get the Saturday
dates to appear in say Col. C, Col. D, Col. E instead of a
row under the date?

 

[Myrna Larson]

Assuming you have 3/28/2004 in A1 and 5/1/2004 in A2 and you want the
Saturdays to be shown starting in C1:

in C1: =A1-WEEKDAY(A1)+7
in D1: =IF(C1+7<=$A$2,C1+7,NA())

Copy the formula in D1 across until it starts returning #NA!

If the starting date is a Saturday, it will be included. If you don't want
that, change the formula for C1 to

=IF(WEEKDAY(A1)=7,A1+7,A1-WEEKDAY(A1)+7)