"Second Tuesday of Every 3 Months"

j-kobb

New Member
Joined
Feb 28, 2012
Messages
2
How can I get excel to tell me the dates of:

The first Tuesday of every month? (this should include 12 dates).
The second Thursday of every three months? (this is 4 dates).
The third Friday of every month? (this is 12 dates).

I am trying to come up with a way to establish the dates of cycle counts. The Oracle Inventory Management System requires dates in format I listed above. I must use Excel to provide the dates (i.e. 01/01/2012) that I can use to communicate with the Operations department. (There are many more than just three dates that I will calculate).
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Prepare a sheet as described below, then turn on autofilters and then by filtering 2 columns you'll get your desired result:

Instructions on creating the sheet:
Header row is in row 1
Columns are A thru D (Date, Day, Month, Sequence are the header names respectively)
Enter 1/1/12 in A2 and then use "fill" to automatically add the rest of the dates in the year in col A.
Enter this formula in B2: =DAY(A2) then copy down
Enter this formula in C2: =MONTH(A2) then copy down
Enter this formula in D2: =IF(DAY(A2)<8,1,IF(DAY(A2)<15,2,IF(DAY(A2)<22,3,IF(DAY(A2)<29,4,5)))) then copy down
Now turn on autofilters. (ie select cells A1:A4, click "Data-Filter") .
To get the 1st Tues of each month, filter Col B for "Tuesday" and Col D for 1
Same theory to filter for other options - if you want every 3 months, just uncheck unwanted months in month filter along with other 2 filters.

Sample of completed sheet:

<table style="width: 313px; height: 740px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:72pt" width="96" height="17">date</td> <td class="xl25" style="width:62pt" width="83">day</td> <td style="width:72pt" width="96">month</td> <td style="width:48pt" width="64">sequence</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/1/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/2/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/3/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/4/2012</td> <td class="xl25" align="right">Wednesday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/5/2012</td> <td class="xl25" align="right">Thursday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/6/2012</td> <td class="xl25" align="right">Friday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/7/2012</td> <td class="xl25" align="right">Saturday</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/8/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/9/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/10/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/11/2012</td> <td class="xl25" align="right">Wednesday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/12/2012</td> <td class="xl25" align="right">Thursday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/13/2012</td> <td class="xl25" align="right">Friday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/14/2012</td> <td class="xl25" align="right">Saturday</td> <td align="right">1</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/15/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/16/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/17/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/18/2012</td> <td class="xl25" align="right">Wednesday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/19/2012</td> <td class="xl25" align="right">Thursday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/20/2012</td> <td class="xl25" align="right">Friday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/21/2012</td> <td class="xl25" align="right">Saturday</td> <td align="right">1</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/22/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/23/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/24/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/25/2012</td> <td class="xl25" align="right">Wednesday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/26/2012</td> <td class="xl25" align="right">Thursday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/27/2012</td> <td class="xl25" align="right">Friday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/28/2012</td> <td class="xl25" align="right">Saturday</td> <td align="right">1</td> <td align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/29/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">1</td> <td align="right">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/30/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">1</td> <td align="right">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/31/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">1</td> <td align="right">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/1/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/2/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/3/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/4/2012</td> <td class="xl25" align="right">Wednesday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/5/2012</td> <td class="xl25" align="right">Thursday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/6/2012</td> <td class="xl25" align="right">Friday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/7/2012</td> <td class="xl25" align="right">Saturday</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/8/2012</td> <td class="xl25" align="right">Sunday</td> <td align="right">2</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/9/2012</td> <td class="xl25" align="right">Monday</td> <td align="right">2</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/10/2012</td> <td class="xl25" align="right">Tuesday</td> <td align="right">2</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/11/2012</td> <td class="xl25" align="right">Wednesday</td> <td align="right">2</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/12/2012</td> <td class="xl25" align="right">Thursday</td> <td align="right">2</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/13/2012</td> <td class="xl25" align="right">Friday</td> <td align="right">2</td> <td align="right">2</td> </tr> </tbody></table>
 
Upvote 0
j-kobb,
You've probably realized this already, but I wanted to correct a typo in my solution. The formula for B2 should be =WEEKDAY(B2), not day(B2). The results returned will be 1 thru 7 where Sun=1, Mon=2, etc. You can format col b as custom dddd which will display the days in words. I apologize for that error.
Ron
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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