Is there a function to return all Thursdays in a given month/yr

[Week Dates from nth day of week]

Using EXCEL, is there a way to return the dates for the 1st, 2nd, 3rd, 4th,
and 5th Wednesday of a month by inputting the month and year?

 

[Biff]

Hi!

Here's one way. This will return all the specific weekday dates between 2
dates (inclusive).

Enter the date span that you want to use in 2 cells. Since you only want a
specific month a very simple formula is used to calculate the end of the
month: B1 will be 8/31/2005

A1 = 8/1/2005
B1 = formula

=DATE(YEAR(A1),MONTH(A1)+1,0)

You can also use a cell to hold the number of the weekday you're interested
in:

C1 = 3 (where Monday = 1, Tuesday = 2, ...... Sunday = 7)

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(WEEKDAY(ROW(INDIRECT($A$1&":"&$B$1)),2)=C$1,ROW(INDIRECT($A$1&":"&$B$1))),ROW(1:1))),"",SMALL(IF(WEEKDAY(ROW(INDIRECT($A$1&":"&$B$1)),2)=C$1,ROW(INDIRECT($A$1&":"&$B$1))),ROW(1:1)))

Copy down 5 cells (the most you'll need for a single month) and format as
DATE.

There's a shorter formula floating around out there that will return the
date for the nth weekday that might also be used for this. I saw it just the
other day. Maybe someone will chime in with that. 'Til then, the above
works!

Biff

"Week Dates from nth day of week" <Week Dates from nth day of
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Krishnakumar]

Hi,

8/1/2005 in A1

To get all Thursdays;

In B1 and copy down 5 rows

=($A$1-WEEKDAY($A$1,3)+*3*)+((ROW()-ROW($B$1)+1)*7-7)

change the bold value to 2 for Wednesdays

HTH

 

[Biff]

Care to explain the logic?

Also:

ROW()-ROW($B$1)

Why do you need ROW($B$1) ?

ROW()-1 does the same thing.

Biff

"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Phillips]

or even

ROW()-ROW($B$1)+1

to

ROW()

<vbg>

Actually, it may be me, but both your formula seem to miss the first
instance. This works for me, and also ignores the 5th if not the correc t
month

=IF(MONTH(DATE(YEAR($A$1),MONTH($A$1),1+7*ROW(B1)-ROW($B$1))-WEEKDAY(DATE(YE
AR($A$1),MONTH($A$1),8-4)))<>MONTH(DATE(YEAR($A$1),MONTH($A$1),1+7*ROW($B$1)
)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),8-4))),"",DATE(YEAR($A$1),MONTH($A$1),
1+7*ROW(B1)-ROW($B$1))-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),8-4)))

 

[Krishnakumar]

Hi,

ROW()-ROW($B$1)

Why do you need ROW($B$1) ?

ROW()-1 does the same thing.

Biff

Did you try with ROW()-1? What was the result?

I clearly mentioned that the formula starts from B1.
ROW()-1 will work if the formula in B2

HTH

 

[Bob Phillips]

That might be true if you had used

ROW(B1)-ROW($B$1)

but I still get a problem with your formula in that it doesn't give the
first instance. If I remove the +1 sometimes it gives from the previous
month.

--
HTH

Bob Phillips

"Krishnakumar" <[email protected]>
wrote in message

 

[Krishnakumar]

Hi,

or even

ROW()-ROW($B$1)+1

to

ROW()

See what will happen if insert a row before A1

You can use Conditional Formating if there is no 5th occurance.

in CF, formula is

=MONTH($A$1)<>MONTH($B$5)

change font colour to white.

HT

 

[Krishnakumar]

Hi Bob,

Yes, with some months there is some problem as it shows previou
month's date.

In this case I would suggest apply conditional formatting for al
occurances.

In CF formula is,

=MONTH($A$1)<>MONTH($B1)

Use format painter to copy this format for other cells.

HT

 

[Ron Rosenfeld]

Using EXCEL, is there a way to return the dates for the 1st, 2nd, 3rd, 4th,
and 5th Wednesday of a month by inputting the month and year?

Not sure if you want the Thursdays, as in the thread subject, or Wednesdays, as
in the Text.

Also, it is not clear how you want these results returned, or how your
worksheet is laid out.

In any event:

With any date in A1, the first Wednesday of that month is returned by:

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

The first Thursday would be:

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

or, more generally:

=A1-DAY(A1)+1+weeknum*7-WEEKDAY(A1-DAY(A1)+8-weekday)

where for weekday, 1=Sunday and 7=Saturday.

You could also have the above formula in, let us say, B1; and in B2 have =B1+7
and copy/drag that down as far as needed.

To check if the 5th NDay is in the same month, so as to leave a blank if it is
not, can be done in a variety of ways. But the "best" method would probably
depend on how you are laying out your worksheet.


--ron

 

[Biff]

Hi!

Yes, I tried with that change and it worked correctly.

=($A$1-WEEKDAY($A$1,3)+3)

I would still like an explanation of the logic behind the above portion of
the formula. How do you arrive at that?

What good is a formula if one doesn't understand it?

Biff

"Krishnakumar" <[email protected]>
wrote in message

 

[Krishnakumar]

Hi,

=($A$1-WEEKDAY($A$1,3)+3)

I would still like an explanation of the logic behind the above portio
of
the formula. How do you arrive at that?

See 'this' (http://www.excelforum.com/showthread.php?t=392972)

HT