Finding the 2nd Tuesday and 2nd Wednesday of Every Month & Labeling them

**rbcmp3** · 03-09-2010, 08:00 PM

Hi All,

OK, here is my problem:
My bank cycles start on the 2nd Wednesday of each month. So, I created a calendar in Excel where I identify the cycle Start Dates. The formula looks like this:
=IF(AND(E100>7,E100<15,G100=3),"START",0)
In Column E are days of month, from 1 through 31
I figure the 2nd Wednesday of the month has to start no earlier than the 8th day (hence >7) and has to end no later than the 14th day (hence <15).

In Column G, I have weekdays, 1 though 7.

So, now the column with that formula either gives me “START” or 0.

Where I need help is the next step:

I want the formula to tell me if this is the cycle END, which is just one day prior, or 2nd Tuesday of the month. I tried to play with NESTED IF but it got confusing.
Any help is appreciated. Thanks!

**xenixman** · 03-10-2010, 02:15 AM

Not sure of your set up, put you can try using the =EOMONTH() and =WEEKDAY() functions to determine the 2nd Tuesday.

If you have Today’s date in A1,
Then in cell B1 you can put =EOMONTH(A1,0)
This will give you the date of end of the current month
Then simply add 14 days, check using =WEEKDAY() to see if it’s a Tuesday,
If it’s not Tuesday then try 13 days, etc. etc. down to 8 days.

Something along the lines of:

=IF(WEEKDAY(B1+14)=3,B1+14,
IF(WEEKDAY(B1+13)=3,B1+13,
IF(WEEKDAY(B1+12)=3,B1+12,
IF(WEEKDAY(B1+11)=3,B1+11,
IF(WEEKDAY(B1+10)=3,B1+10,
IF(WEEKDAY(B1+9)=3,B1+9,
IF(WEEKDAY(B1+8)=3,B1+8,"Error")))))))

**DonkeyOte** · 03-10-2010, 03:06 AM

I suspect a sample file would help here (I for one am struggling to visualise)

You can determine the 2nd Wed of any given month using:

=A1+7+CHOOSE(WEEKDAY(A1,2),2,1,0,6,5,4,3)
where A1 holds the 1st of Month

It follows that 2nd Tuesday is the above less 1 (dates are Integers).

**rbcmp3** · 03-10-2010, 11:17 AM

Great. Thank you very much. Both suggestions help. Thanks a lot!

**xenixman** · 03-10-2010, 06:52 PM

Here's a minor modification to DonkeOte's formula,
With this any date is acceptable and will give you the following months 2nd Tuesday.
Enter a date in Jan. it will give you the Feb. 2nd Tuesday

=((EOMONTH(A1,0)+1)+7)+CHOOSE(WEEKDAY((EOMONTH(A1,0)+1)),2,1,0,6,5,4,3)

**rbcmp3** · 03-11-2010, 02:14 AM

That works beautifully. Thank you so much!

**MrBrat2U** · 08-06-2014, 05:37 AM

The first row are titles
Then a2 cell is the first day of the current month, e.g. 08/01/2014
For a3 thru however many months you want:
=A2+IF(MONTH(A2)=2,28,IF(MONTH(A2)=9,30,IF(MONTH(A2)=11,30,IF(MONTH(A2)=4,30,(IF(MONTH(A2)=6,30,31))))))
For b2 thru the end:
=A2+7+CHOOSE(WEEKDAY(A2,2),2,1,0,6,5,4,3)
I also use c2 to cross check the day of the week:
=WEEKDAY(B2)

Note that for each February that is a leap year (or USA national elections year):
Change the formula from 2,28 to 2,29 (for just the month of February)

Enjoy!!