Find the First Wednesday of a month

New2excel

New Member
Joined
Jul 10, 2004
Messages
11
Hi

In my worksheet I would like to type in A1 a month and year (ex. July 2005)

In another cell on page one, I would like the date of the first Wednesday of the month and year in A1 (ex. 7/6). (This us used to calculate other dates on page one of the worksheet.)

On the next page of the worksheet, I would need the second Wednesday of the month and year in A1 (7/13).

On the next page I would need the Third Wednesday, etc. through the end of the month.

Then I need to know how how many pages needed until last Wed. in the month (depends on if the month has 4 weeks, or 5 weeks)

I hope this makes sense.......... :wink:

Thanks for any help.

Suz
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
If you type "July 2005" in a cell excel would normally interpret this as 1st July 2005 (although you can format the cell as "mmmm yyyy" to show "July 2005".

So assuming the actual date in A1 is the 1st of the month

=A1+7-WEEKDAY(A1+3)

will give the first Wednesday

If that formula is in A2 then for the next Wednesday you just need

=A2+7

For the number of Wednesdays in the month, based on the 1st of that month in A1

=4+(DAY(A1+35-WEEKDAY(A1+3))>7)

format as general
 
Upvote 0
I'm sure someone will come around with a simpler way, perhaps using the morefunc add in.

But here's an array formula that will give list each wednesday in a given month. Month is is A2, Year is in A3.

In B5:
=SMALL(IF((WEEKDAY(DATE($B$2,$A$2,ROW(A$2:A$46)))=4)*(MONTH(DATE($B$2,$A$2,ROW(A$2:A$46)))=$A$2),
DATE($B$2,$A$2,ROW(A$2:A$46)),
""),{1;2;3;4;5})

Now confirm this array entered formula across 5 or more cells. That is, put your cursor in the formula bar for B5, now highlight B5 to B11. Press control + shift + enter at the same time.
 
Upvote 0
If you type "July 2005" in a cell excel would normally interpret this as 1st July 2005 (although you can format the cell as "mmmm yyyy" to show "July 2005".

So assuming the actual date in A1 is the 1st of the month

=A1+7-WEEKDAY(A1+3)

will give the first Wednesday

If that formula is in A2 then for the next Wednesday you just need

=A2+7

For the number of Wednesdays in the month, based on the 1st of that month in A1

=4+(DAY(A1+35-WEEKDAY(A1+3))>7)

format as general

Hello, I have been using the above formula without a hitch until the the first of the month fell on the first Wednesday of the month. Any help?

Thanks
 
Upvote 0
Hello, I have been using the above formula without a hitch until the the first of the month fell on the first Wednesday of the month

I think all the formulas work as expected - which one are you referring to? If you mean the one for counting Wednesdays in a month then when the 1st of the month is a Wednesday there will always be 5 Wednesdays in the month except for Februarys in non leap years and I think the suggested formula calculates that correctly,

Month Start<form name="inlinemodform" id="inlinemodform" action="https://www.mrexcel.com/forum/inlinemod.php?threadid=152093&p=" method="post">No of Weds </form>
Wed 1-Nov-175
Wed 1-Aug-185
Wed 1-May-195
Wed 1-Jan-205
Wed 1-Apr-205
Wed 1-Jul-205
Wed 1-Sep-215
Wed 1-Dec-215
Wed 1-Jun-225
Wed 1-Feb-234
Wed 1-Mar-235
Wed 1-Nov-235
Wed 1-May-245
Wed 1-Jan-255
Wed 1-Oct-255

<tbody>
</tbody><colgroup><col><col></colgroup>
 
Last edited:
Upvote 0
I'm sure someone will come around with a simpler way, perhaps using the morefunc add in.

But here's an array formula that will give list each wednesday in a given month. Month is is A2, Year is in A3.

In B5:
=SMALL(IF((WEEKDAY(DATE($B$2,$A$2,ROW(A$2:A$46)))=4)*(MONTH(DATE($B$2,$A$2,ROW(A$2:A$46)))=$A$2),
DATE($B$2,$A$2,ROW(A$2:A$46)),
""),{1;2;3;4;5})

Now confirm this array entered formula across 5 or more cells. That is, put your cursor in the formula bar for B5, now highlight B5 to B11. Press control + shift + enter at the same time.

Small modification...
Your formula should be:
=SMALL(IF((WEEKDAY(DATE($B$2,$A$2,ROW(A$2:A$46)-1))=4)*(MONTH(DATE($B$2,$A$2,ROW(A$2:A$46)-1))=$A$2),DATE($B$2,$A$2,ROW(A$2:A$46)-1),""),{1;2;3;4;5})

to take care if first day in month is Wednesday
 
Upvote 0
to take care if first day in month is Wednesday

Thanks Ingolf, so I assume tobyz95 was referring to that formula....

For a multi-cell array formula to return all Wednesdays in the month this should be sufficient, entered in a vertical array of 5 cells

=IFERROR((B2&"-"&A2&"-"&{1;2;3;4;5}*7+1-WEEKDAY(B2&"-"&A2&"-4"))+0,"")

confirm with CTRL+SHIFT+ENTER

Where A2 contains the month as a number and B2 the year as a number

For other days change the red 4 accordingly: 1=Sat through to 7=Sun

This will leave a blank in the 5th cell if there is no 5th day in the month
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top