DATE LISTING: Second and Fourth WEDNESDAYS

[AS400Tech]

I seem to always be pushing the limits on what Excel was designed for ...and here I go again:

I need to list all 2nd & 4th Wednesdays in two columns, for an entire year. I'd like to manually change the year in one cell and dynamically update the dates. Columns are Month-MMM, Day-dd (2nd Wed.), Day-dd (4th Wed.). I haven't decided whether I want a 3rd Day column on the rare occasion theres FIVE WEDNESDAYS in that month, which makes the formula easier if we're only adding 14 days to the previous date.

I'm certain at least a dozen companies would benefit from such a Spreadsheet, but this challenge pushes the limits of my expertise. Thanks.

 

[elmer007]

I've created the table below with the formulas noted (starts in cell A1 with 2015):

[TABLE="width: 528"]
<tbody>[TR]
[TD]2015
[/TD]
[TD]Month-MMM
[/TD]
[TD]Day-dd (2nd Wed.)
[/TD]
[TD]Day-dd (4th Wed.)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/1/2015
[/TD]
[TD]1/14/2015
[/TD]
[TD]1/28/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/1/2015
[/TD]
[TD]2/11/2015
[/TD]
[TD]2/25/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/1/2015
[/TD]
[TD]3/11/2015
[/TD]
[TD]3/25/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4/1/2015
[/TD]
[TD]4/8/2015
[/TD]
[TD]4/22/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/1/2015
[/TD]
[TD]5/13/2015
[/TD]
[TD]5/27/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/1/2015
[/TD]
[TD]6/10/2015
[/TD]
[TD]6/24/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/1/2015
[/TD]
[TD]7/8/2015
[/TD]
[TD]7/22/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8/1/2015
[/TD]
[TD]8/12/2015
[/TD]
[TD]8/26/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/1/2015
[/TD]
[TD]9/9/2015
[/TD]
[TD]9/23/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/1/2015
[/TD]
[TD]10/14/2015
[/TD]
[TD]10/28/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/1/2015
[/TD]
[TD]11/11/2015
[/TD]
[TD]11/25/2015
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/1/2015
[/TD]
[TD]12/9/2015
[/TD]
[TD]12/23/2015
[/TD]
[/TR]
</tbody>[/TABLE]


Cell A1: 2015
Cell B2: =VALUE("1/1/"&A1)
Cell B3: =EOMONTH(B2,0)+1 ...and copy down
Cell C2: =IF(WEEKDAY(B2,1)>4,(B2+((7-WEEKDAY(B2,1))+4))+7,B2+((7-WEEKDAY(B2,1))+4)) ...and copy down
Cell D2: =C2+14 ...and copy down


Change the year to change in cell A1 to change the table.

I've only just tested it with 2015 and 2016, but I think I got the formulas right...

Also, 5 Wednesdays in a month happens frequently (I think 4 times a year almost every year, so 33% of months). If column E is added to show the 5-Wednesday months, then E2: =IF(MONTH(D2+7)=MONTH(D2),D2+7,"") ...and copy down

[TABLE="width: 683"]
<tbody>[TR]
[TD]2015[/TD]
[TD]Month-MMM[/TD]
[TD]Day-dd (2nd Wed.)[/TD]
[TD]Day-dd (4th Wed.)[/TD]
[TD]Day-dd (5th Wed.)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/1/2015[/TD]
[TD]1/14/2015[/TD]
[TD]1/28/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/1/2015[/TD]
[TD]2/11/2015[/TD]
[TD]2/25/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/1/2015[/TD]
[TD]3/11/2015[/TD]
[TD]3/25/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4/1/2015[/TD]
[TD]4/8/2015[/TD]
[TD]4/22/2015[/TD]
[TD]4/29/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/1/2015[/TD]
[TD]5/13/2015[/TD]
[TD]5/27/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/1/2015[/TD]
[TD]6/10/2015[/TD]
[TD]6/24/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/1/2015[/TD]
[TD]7/8/2015[/TD]
[TD]7/22/2015[/TD]
[TD]7/29/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8/1/2015[/TD]
[TD]8/12/2015[/TD]
[TD]8/26/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/1/2015[/TD]
[TD]9/9/2015[/TD]
[TD]9/23/2015[/TD]
[TD]9/30/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/1/2015[/TD]
[TD]10/14/2015[/TD]
[TD]10/28/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/1/2015[/TD]
[TD]11/11/2015[/TD]
[TD]11/25/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/1/2015[/TD]
[TD]12/9/2015[/TD]
[TD]12/23/2015[/TD]
[TD]12/30/2015[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

 

[Jonmo1]

Here's a formula to find the nth weekday of January in a given Year

=DATE($A$2,ROWS($1:1),1)+$B$2-WEEKDAY(DATE($A$2,ROWS($1:1),1))+($C$2-($B$2>=WEEKDAY(DATE($A$2,ROWS($1:1),1))))*7

A2 is the Year
B2 is the desired weekday (1=Sun, 2=Mon, 3=Tue, etc)
C2 is the Nth (2nd weekday...)
Then ROWS($1:1) determines the month number, auto adjusts as you drag it down.

Then you can simply add 14 to get the 4th Wednesday.
Then add 7 to that to get the 5th, adding an if to test if it's still in the same month.

Example

Excel Workbook
	A	B	C	D	E	F
1	Year	Weekday	Nth	2nd Wednesday	4th Wednesday	5th Wednesday
2	2015	4	2	1/14/2015	1/28/2015
3				2/11/2015	2/25/2015
4				3/11/2015	3/25/2015
5				4/8/2015	4/22/2015	4/29/2015
6				5/13/2015	5/27/2015
7				6/10/2015	6/24/2015
8				7/8/2015	7/22/2015	7/29/2015
9				8/12/2015	8/26/2015
10				9/9/2015	9/23/2015	9/30/2015
11				10/14/2015	10/28/2015
12				11/11/2015	11/25/2015
13				12/9/2015	12/23/2015	12/30/2015
Sheet1

 

[AS400Tech]

DATE LISTING: First and Third WEDNESDAYS

That's awesome guys, thanks! I goofed on one minor detail, it was supposed to be First and Third Wednesday instead. Plus, the sheet is intended for publishing to regular office personnel so I had to massage for readability sake. The only place I got hung up on the modifications is the FIFTH Wednesday part. I did determine that if the first Wednesday is less than 3, then theres a 5th Wednesday, but I spent an hour trying to get that formula to work to no avail. Its probably something simple but I keep missing it. Heres what I have so far:


“January” A1=B2 [formatted (mmmm)]

(B2 is Hidden for Readability Sake)

“4,” =IF(WEEKDAY($B2,1)>4,($B2+((7-WEEKDAY($B2,1))+4)),$B2+((7-WEEKDAY($B2,1))+4)) [formatted "d"]

“21,” =C2+14 [formatted "d"]

* need column for 5th Wednesday [formatted "d"] conditioned if NO 5th Wednesday then leave blank

2015
January 7, 21
February 11, 25
March 11, 25
April 8, 22
May 6, 20
June 10, 24
July 8, 22
August 5, 19
September 9, 23
October 7, 21
November11, *
December 9, *

* = Closed for Holiday

 

[elmer007]

Re: DATE LISTING: First and Third WEDNESDAYS

To adjust to the 1st and 3rd Wednesdays, just tack -7 on to the end of the original formula in cell C2: =IF(WEEKDAY(B2,1)>4,(B2+((7-WEEKDAY(B2,1))+4))+7,B2+((7-WEEKDAY(B2,1))+4))-7
For the 5th Wednesday formula, change the 7s to 14s: E2: =IF(MONTH(D2+14)=MONTH(D2),D2+14,"")

 

[AS400Tech]

DATE LISTING: Every Second WEDNESDAY

Thanks Elmer. My staff clarified that I had it messed up twice here. I guess its just every 14 days on Wednesday, so that there will never be a 3rd Column. I should have enough to tinker with here to get anything I need from this point. Thanks again.

 

[barry houdini]

=IF(WEEKDAY(B2,1)>4,(B2+((7-WEEKDAY(B2,1))+4))+7,B2+((7-WEEKDAY(B2,1))+4))

Generically with the 1st of the month in B2 you can get the nth "x-day" of that month by using this formula

=B2+7*n-WEEKDAY(B2-x)

where x is a number 1 to 7 (Sunday = 1 through to Saturday = 7)

so for 2nd Wednesday, for example, you can use just

=B2+14-WEEKDAY(B2-4)

 

[AS400Tech]

DATE LISTING: Second WEDNESDAYS, Weekday Calculations, etc.

Awesome Barry. Now EVERYONE in Offices Nationwide are completely set and can readily generate and post their "Meeting Schedules," of any type, every year!

All this is just so fantastic! Thanks again everyone!

Rich