How to list dates in a month of particular weekday

[MasterDragon]

MY question is simple, sorry if i'm being stupid.

Can anyone tell me how can i make a list which contains only dates of all
the Fridays in a particular month?

In a more advanced sense, can I create a list with all the Fridays AND
Saturdays of a month?

 

[Rick Rothstein]

You didn't post a lot of detail about your setup. Assuming the month number
is in A1 and your list of Fridays and Saturdays is to be listed in Column B
starting at B1, put these formulas in the indicated cells...

B1: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,2))
B2: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,1))
B3: =IF(B1="","",IF(MONTH(B1+7)=$A$1,B1+7,""))

And then copy down the formula that is in B3 down to B10.

 

[Jeff]

MSDN has a good exaple of finding a day
http://msdn.microsoft.com/en-us/library/aa227532(VS.60).aspx

 

[Rick Rothstein]

Just so you don't think those formulas were mystically arrived at, here is
the generic form for calculating the nth such and such day of a month...

The generic version of the formula is this...

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where nth is the number you want 1st, 2nd, 3rd etc thus in my first formula
7*1 since you wanted the 1st Friday and where DoW stands for day of the week
with Sunday starting with 1 and so on and where I put 6 for Friday. So, if
you want the 1st Friday of the month number in A1 it would look like

=DATE(YEAR(NOW()),$A$1,1+7*1)-WEEKDAY(DATE(YEAR(NOW()),$A$1,8-6))

which reduces to the first formula I listed.

Note: I don't know if this generic formula was original with him or not, but
I first saw it posted online by Peo Sjoblom.

 

[Mike H]

Hi,

With a properly formatted date in a1 try this and drag down for the Fridays
of that month in A1

The key to Getting the other days of the weekdays lies in the -1 in this bit
of the formula
ROWS(A$1:$A1)*7)-1
Change the -1 to different values (It appears 3 times) and you'll figure it
out

=IF(MONTH(($A$1-WEEKDAY($A$1,1))+(ROWS(A$1:$A1)*7)-1)<>MONTH($A$1),(($A$1+7-WEEKDAY($A$1+7,1))+(ROWS(A$1:$A1)*7)-1),($A$1-WEEKDAY($A$1,1))+(ROWS(A$1:$A1)*7)-1)



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

By the way, I gave you a solution for your second question; however, if you
only wanted to list the Fridays by themselves, use the same formula I gave
you for B1, but use the formula I gave you for B3 in B2 instead and then
copy it down.

 

[Mike H]

On reflection that was a bit misleading because it isn't always a -1 value so
i'll do it for your. The numbers are

-1 fri
0 sat
1 sun
2 mon
3 tue
4 wed
5 thu

Mike
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Here is a much shorter function to do what the function at the link does...

Function NthWeekday(DateIn As Date, Nth As Long, DOW As Long) As Date
NthWeekday = DateSerial(Year(DateIn), Month(DateIn), 1 + 7 * Nth) - _
Weekday(DateSerial(Year(DateIn), Month(DateIn), 8 - DOW))
End Function

This function is just the general formula I posted elsewhere in this thread
converted to a one-liner VB statement.

 

[Ron Rosenfeld]

MY question is simple, sorry if i'm being stupid.

Can anyone tell me how can i make a list which contains only dates of all
the Fridays in a particular month?

In a more advanced sense, can I create a list with all the Fridays AND
Saturdays of a month?

With some date in the month of interest in A1

First Friday of the month:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)

Or, to generalize:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW)

Where DOW = Day of Week (Sun = 1)

So the first Saturday would be:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1)

So, to get all the Fridays in a given month, again with some date in that month
in A1:

B1:

=IF(MONTH($A$1-DAY($A$1)+8-WEEKDAY($A$1-DAY($A$1)+2)+
(ROWS($1:1)-1)*7)<>MONTH($A$1),"",$A$1-DAY($A$1)+8-
WEEKDAY($A$1-DAY($A$1)+2)+(ROWS($1:1)-1)*7)

This tests to make sure the month output is the same as the month of A1.

The same, obviously, would apply for Saturdays.
--ron

 

[Ron Rosenfeld]

B1:

=IF(MONTH($A$1-DAY($A$1)+8-WEEKDAY($A$1-DAY($A$1)+2)+
(ROWS($1:1)-1)*7)<>MONTH($A$1),"",$A$1-DAY($A$1)+8-
WEEKDAY($A$1-DAY($A$1)+2)+(ROWS($1:1)-1)*7)

This tests to make sure the month output is the same as the month of A1.

The same, obviously, would apply for Saturdays.

Forgot to state to "fill down" at least five rows.
--ron