Rotational Date Changes

[SAM]

I am setting up a spreadsheet that has dates. I have made it so I can change
the month and the year. How do I get a formula of another cell to figure the
saturdays and place them in a cell. After that I am going to insert a formula
for data for that date. Please help me out.

 

[Bob Phillips]

All of the saturdays, or just the first?

 

[SAM]

All of the Saturdays. Is there anyway that the computer would know this?

 

[Bob Phillips]

You could use this array formula

=IF(INDEX(A:A,SMALL(IF(WEEKDAY(A1:A100)=7,ROW(A1:A100)),ROW(A1)))=0,"",
INDEX(A:A,SMALL(IF(WEEKDAY(A1:A100)=7,ROW(A1:A100)),ROW(A1))))

 

[SAM]

Thank you but it doesn't work. Basically what I need is for the computer,
some way to be able to know where to start counting the days based on the
month and end the formula every Saturday. This would be able to roate based
on the month. Knowingly that each month has a different amount of days. I am
not sure that this is possible but if anyone knows please help me out. Thanks

P.S. The month that I am trying to use right now is May...if that helps! : )

 

[Bob Phillips]

You will need to explain this better ... start counting the days based on
the month and end the formula every Saturday

 

[SAM]

Ok... picture this:

A spredsheet with 3 columns
example:

May 1 08
May 2 08
....

At the bottom of the page I have 5 rows for each Sat.

May 03 08
May 10 08
May 17 08
May 24 08
May 31 08

To the right of these I have data info from each week but my problem is I
want the program to automatically change the dates to the months Sat. For
instance when it is a June month I want it to look the same only with the
right days. If you need more info reply back. Thanks so far for your help.

 

[Rick Rothstein \(MVP - VB\)]

I'm afraid your layout is still not completely clear (for example, "bottom
of page"). Anyway, let's assume the date for the current month is in A1 and
the first Saturday shown at "the bottom of the page" is in cell A35 (adjust
the ranges for wherever it really is), then put these formula in the
indicated cells...

A35: =A1-DAY(A1)+7-MOD(WEEKDAY(A1-DAY(A1)),7)
A36: =A35+7
A37: =A36+7
A38: =A37+7
A39: =IF(MONTH(A38+7)=MONTH(A38),A38+7,"")

This will list the four sure Saturdays and the fifth one when present.

Rick