Determine date based on day of month (i.e. 2nd Tuesday of the month)

I've used Excell for years and I'm usually pretty good at finding
answers to my questions.  However, this one has eluded me.  I figure
the answer is simple and I'll have a "DOH!" moment when it's pointed
out...

I'm trying to determine the date of the 2nd Tuesday of every month in
2007.

Ideally, I'd have two columns.  First would have the month, the second
would have the specific date of the 2nd Tuesday for that month.

Can someone kindly point me in the right direction?  I can't seem to
figure this one out.

0
CSB001 (2)
8/9/2006 12:37:09 PM
excel 39879 articles. 2 followers. Follow

4 Replies
792 Views

Similar Articles

[PageSpeed] 23

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"csb" <CSB001@gmail.com> wrote in message news:1155127029.076377.257100@h48g2000cwc.googlegroups.com...
| I've used Excell for years and I'm usually pretty good at finding
| answers to my questions.  However, this one has eluded me.  I figure
| the answer is simple and I'll have a "DOH!" moment when it's pointed
| out...
|
| I'm trying to determine the date of the 2nd Tuesday of every month in
| 2007.
|
| Ideally, I'd have two columns.  First would have the month, the second
| would have the specific date of the 2nd Tuesday for that month.
|
| Can someone kindly point me in the right direction?  I can't seem to
| figure this one out.
| 


0
nicolaus (2022)
8/9/2006 12:45:50 PM
=DATE(YEAR(A1),MONTH(A1),1+7*2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-3))

where A1 holds a date

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"csb" <CSB001@gmail.com> wrote in message
news:1155127029.076377.257100@h48g2000cwc.googlegroups.com...
> I've used Excell for years and I'm usually pretty good at finding
> answers to my questions.  However, this one has eluded me.  I figure
> the answer is simple and I'll have a "DOH!" moment when it's pointed
> out...
>
> I'm trying to determine the date of the 2nd Tuesday of every month in
> 2007.
>
> Ideally, I'd have two columns.  First would have the month, the second
> would have the specific date of the 2nd Tuesday for that month.
>
> Can someone kindly point me in the right direction?  I can't seem to
> figure this one out.
>


0
bob.NGs1 (1661)
8/9/2006 1:02:08 PM
SOLVED!

Thank you to Niek and Bob for the solutions.  Both work great!

I really appreciate your assistance.

0
CSB001 (2)
8/9/2006 1:18:27 PM
On 9 Aug 2006 05:37:09 -0700, "csb" wrote in microsoft.public.excel:

>I've used Excell for years and I'm usually pretty good at finding
>answers to my questions.  However, this one has eluded me.  I figure
>the answer is simple and I'll have a "DOH!" moment when it's pointed
>out...
>
>I'm trying to determine the date of the 2nd Tuesday of every month in
>2007.
>
>Ideally, I'd have two columns.  First would have the month, the second
>would have the specific date of the 2nd Tuesday for that month.
>
>Can someone kindly point me in the right direction?  I can't seem to
>figure this one out.

Based on a formula on CP's web site, as pointed out by Niek:
  =A1+(WEEKDAY(A1)>=3)*7-WEEKDAY(A1)+3+7
Finds the first Tuesday of a month and adds 7.

This is a function without an implied If:

  =DATE(YEAR(A1),MONTH(A1),7*2-6+(MOD(3+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)))

where Tuesday (3) and the second occurrence have been hardwired but are still shown.
It is derived from a 4NT function which computes the Qth occurrence of the weekday N:

  : Returns as a date the Q-th occurrence of the weekday N (1-7 = Sun-Sat) for the month of myDate
  : E.g.: @nWkDay[3,1,2005-05-01] whill return the date of the 3rd Sunday in May 2005
  nWkDay=%@MAKEDATE[%@DATE[%@YEAR[%3]-%@MONTH[%3]-%@EVAL[7 * %1 - 6 + (%2 + 7 - %@DOWI[%@YEAR[%3]-%@MONTH[%3]-1]) %% 7]],4]

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"
0
ROT13-zo (57)
8/9/2006 1:51:31 PM
Reply:

Similar Artilces:

question re international characters in e-mail message
I have my keyboard set up as US - International. For me to type the Euro symbol, I simply press CTRL + ALT + 5 and I see €. I just now responded to an e-mail where I could not enter that symbol. I created a new e-mail message and there was no problem for me to enter that symbol. Can someone else's e-mail message have a built in limitation that precludes me from responding using that symbol .... or was this a one time glitch? Thanks, Dave Horne You might have additional keyboard layouts/languages installed which doesn't support that character. See http://www.msoutlook.info/...

Parameter Query using date ranges
I am creating a parameter query where I want to pull data between 2 dates. In the date criteria of my query, if I write between #01/01/2008# and #01/31/2008# +1, I will get all data for January. If I use a form to enter the dates and have Between [Forms]![autoexec]![start] And [Forms]![autoexec]![end] +1 in the date criteria, I get an error. Does anyone know what I should put in the criteria to make the query work? Are you entering your dates as m/d/y? What happens if you try: DateAdd("d", 1, [Forms]![autoexec]![end]) Is the form open? -- Duane Hookom Microsoft Access...

How do I get the header to print out for e-mail messages?
For some reason, I am unable to print out the header of e-mail messages and it only prints out the message but not the time/date, subject, To..., From...., etc. Does anyone have an idea how to get this back on? Aloha yoda_the_webmaster, I'm guessing you're using Outlook 2003 (or earlier?) with Internet Explorer 7. That's a known issue that the IE team is working on. In the meantime you can either try and go back to IE6, upgrade to Outlook 2007 (which is unaffected because it doesn't use IE to print HTML messages) or edit the message and change the format to Rich T...

how do i get "click on" in recieved e mails to work
WHEN I RECEIVE E MAILS WITH "CLICK ON" STATEMENTS TO GO TO SOME OTHER WEB SITE MY OUTLOOK JUST BRINGS UP A BLANK MICROSOFT INTERNET EXPLORER PAGE AND GOES NO WHERE. WHAT SETTINGS DO I HAVE TO CHANGE TO CAUSE THE "CLICK ON" TO GO TO THE INTENDED WEB SITES. WJONES1122 <WJONES1122@discussions.microsoft.com> wrote: > WHEN I RECEIVE E MAILS WITH "CLICK ON" STATEMENTS TO GO TO SOME OTHER > WEB SITE MY OUTLOOK JUST BRINGS UP A BLANK MICROSOFT INTERNET > EXPLORER PAGE AND GOES NO WHERE. WHAT SETTINGS DO I HAVE TO CHANGE TO > CAUSE THE "CLICK ON...

Static Date and Time in Excel 2007
Hello all, I have googled around and have not been able to find the answer on the following: I am looking to have a cell that automatically populates the current date and time and leave that information STATIC in that cell once populated when an adjacent cell has a value entered into it. The only answers I fould were for excel 2000 and 2003. I am using 2007 and I am totally lost with the new menu system that has been implemented here. If this requires a macro, please help me walk through how to enter the area where the macros is to be enter along with any supporting code. Thanks to a...

Move emails automatically from Inbox to other folder, based on dat
Some of my users have thousands of emails in their Inbox which slow down performance. Is there a tool with which I can automatically move items older than 30 days to a different folder in Outlook? Based on message class would also be fine, since I use Vault mail archiving, which changes to message class from IPM.Note to IPM.Note.EnterpriseVault.Shortcut. Thanks! Chiel Varkevisser The Netherlands In news:F458372B-155A-4A83-A1F3-4E860477A3D4@microsoft.com, ChielV <ChielV@discussions.microsoft.com> typed: > Some of my users have thousands of emails in their Inbox which slow > do...

$8.95 Monthly Charge from Microsoft
For the last 3 months, I have received a charge against my primary checking account from Microsoft for $8.95. I have no idea why and no idea who to contact to find out what it is for or how to get it stopped. I did upgrade to Money 2004 recently, but don't recall signing up for any new services. Thanks. Glen One thought: is this a relatively new computer, that came with a free 6-months of MSN (ISP) service? Dell offers that. If that's the case, Dell sets up the MSN account, including billing info such as your credit card number, when you order the computer. After the free 6 mont...

HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical)
HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical) ---------------------------------------------------------------------------------------------------------------------------------------------- Microsoft Business Solutions (Microsoft Small Business Financials) Need one resource each for Functional and Technical roles. Location : Chicago, IL Duration : 12+ Months Funtional : This person will be responsible for the requirements gathering and setting up the system. Technical : This person will be responsible for installing and maintaining th...

Project-based Purchase Receipt Return in GP 9
A user entered a purchase receipt for a non-inventory item against a project and posted it. The receipt has not been billed and never will be so that's not an issue. If the receipt has not been invoiced, should she simply enter a Return transaction? If the receipt has been invoiced but we just want to get rid of the cost from the project, what is the best way to handle this? -- Charles Allen, MVP ...

Empty cells showing that they are default formated to "Date"
I created a spead sheet with one column set as a date the first to the end of the month. The other columns have various in text and accounting info. If I click on a empty cell it show in the format section on the bar the the cell is set up to have a date entry. This is all over the page. Can any one tell me why this is happening? -- Fred You can correct that by setting the formating for the other columns. Simply click on the column(s) then right click and select Format cells, or select the column(s) then go to Format and then Cells. In the selection list choose any option bes...

month end close out
during "close billing cycle" We get message "account receivable balance does not match closing balance". It tell us which account has the problem and says accounts will not be closed. What causes this and how do we fix it? Sam, Not exactly the same error message, but try this. http://tinyurl.com/8utdt -- = "sam" <sam@discussions.microsoft.com> wrote in message news:2ACF22EE-7377-421D-B985-F819E76C263B@microsoft.com... > during "close billing cycle" We get message "account receivable balance > does > not match closing balance&...

use macro to change dates
I have never used macros before but am trying to convert dates using excel 2003. The dates are in the format of +1.123.456.1234 and I would like to change that to 123-456-1234. There are also some listed as 123.456.1234 and would like them to be the same as the first ones, that is, 123-456-1234. I tried using the "start recording" method and "stop recording" but the cursor just jumps back to the original cell when I try to use the recorded macro. I have 2000 cells to change. Anybody have a suggestion on how to change these? I looked at the VBE, but don't kno...

line graph-need to pick up dates as values for the x axis?
How do you get a line graph to pick up and show dates as the values on the x axis, not starting at zero nor at the first of the month. There doesn't seem to be anywhere for x-axis data to be transferred to the x- axis. I did put the dates into a row of cells and highlight them. i am using MS office 2000. Go to Source Data on the Chart menu. On the Series tab, select the series, then click in the XValues box, clear it if necessary, and select the range that contains your dates. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions ht...

Calendar only showing one month
When making a meeting maker all my users even myself (admin) can only see about one month on anyones calendar after that is simply says no information availiable. I know there is informaiton available past the one month it just isn't showing up. It doesn't come up very often but no one can make a meeting maker with anyone past one month out. Is there a setting for this?? You have to tell Outlook to publish more 1. On the Tools menu, click Options, click Calendar Options, and then click Free/Busy options 2. In the "Publish <2> month(s) of Calendar Free/Busy information o...

e-mail banners
Hi, I am look for a way of adding a mail disclaimer to all out bound mail traffice on Exchange 2000. I could add it manually to my users mail signature but would rather have something that was done on an enterprise level. Any suggestion would be greatfully recieved. Thanks. Barry For something done on the enterprise level, you really ought to look at disclaimer software. Google for Exchange Disclaimer or something like that, and you should see several different software vendors that can do this. Or, if you want something cheap, look at GFI MailEssentials. I've heard that you ca...

How do I sum YTD totals based on monthly totals
I have a financial worksheet with expenses by month and a monthly total for each month. At the end of each subsequent month there is a cumulative total of the monthly totals (YTD), I cannot figure out how to get an automatic formula to populate the correct cells to mimick the previous formulas with the current cell data to get the correct totals. -- Donna EMU Alumni "Bsgrad02" <Bsgrad02@discussions.microsoft.com> wrote in message news:8E3201DF-9B59-4188-8E19-BCC24B00012C@microsoft.com... > I have a financial worksheet with expenses by month and a monthly total for &g...

Line chart: Dates in uneven intervalls on x-achsis #3
Great! I've just tried it out: You two were completely right. It work now like I had thought. Thank you so much! Have a great weekend! Gil -- gdavid ----------------------------------------------------------------------- gdavids's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1589 View this thread: http://www.excelforum.com/showthread.php?threadid=27378 ...

Autoarchive email date selection logic
I have noticed that my Outlook 2003 Autoarchive is not deleting my old email. I have checked my global Autoarchive setting and it is turned on. I have set folder specific Autoarchive settings. Most of these are to operate on email older than 4 months, either sending it to the Autoarchive .pst file, or deleting it. This is not happening; I have email much older than a year. I looked this up and read that the Autoarchive process for email goes by the "received date or the last modification date/time, whichever is later." So I looked at both dates for all my email. The probl...

Date functions
Our database represents dates by giving the day number within a year. For example, day number 1 is January 1, and day number 365 is December 31. Is there a function to convert day number to mmm-dd? =TEXT(DATE(2005,1,A1),"mmm-dd") where cell A1 holds the day of the year "Kirk P." <KirkP@discussions.microsoft.com> wrote in message news:847D34F3-CA15-4BA2-A5EB-578DBBB42E6A@microsoft.com... > Our database represents dates by giving the day number within a year. For > example, day number 1 is January 1, and day number 365 is December 31. Is > there a fun...

Formatting a cell for the computer's system date
I've set up a monthly expenses spreadsheet to track my payments, due dates, paid dates, balances, etc. I need to have A1 accept the computer's system date when I make changes and print it out. Help!! ...

e-mail attachments-videos
I'm running Vista, and I've recently ran into a problem. I've always normally been able to open up my attachments that are videos by the usual double click, a futher window pops up to Open, indicating that it'll open up using Windows Media Player, I clicked on Open and it would begin. Now when I do the aforementioned and the window comes up and I click on open, it begins playing, however to view it I have to click on the icon Windows Media Player before the actual e-mail will disappear and the video is able to be viewed. Why would this have just occurred, has ...

Cannot add sound or video any more to Outlook E-mail (Word Mail) message!
Using Windows XP Pro and Office 2003. Recently updated both to their newest service packs. (including xpsp2). Word (2003) is set as my e-mail editor for Outlook 2003. Before I could add video, audio, or marquee text to an Outlook E-mail message by clicking on the appropriate icon on the "Web Tools" toolbar. Now when I click on one of those icons, I get an error message like the following: ---------------------- The object was created in HTMLInlineVideoCtl. This application is not available to open this object. Make sure the application is properly installed, and that it has ...

Adding 2nd email acct. to Outlook 2002
Hi: In older versions of Outlook you were able to add/view an 2nd email acct by setting permission on the Exchange server and setting it up as a service on the local PC. It appears 2002 gives the option to add an email acct. under tools but then gives an error saying only 1 acct can be on the exchange server. I would love to know the steps for allowing a user to see two different email messages under 1 Outlook icon. Thanks, A :-) you can only have one Exchange account per profile. "Air" <anonymous@discussions.microsoft.com> wrote in message news:cac301c48a1a$75b6...

80040e19 error occurring with 2nd NIC installed
I am experiencing the 80040e19 error when expanding public folders in Exchange System Manager, Exchange 2000. I have exhausted all suggested MS articles and *still* have not resolved the issue. No evidence of URLScan or iislockdown being run on the server. Strange thing is is that when I disable the newly installed NIC through device manager the public folders can be expanded perfectly. On enabling the NIC, same error. The server is the PDC, Exchange and Intranet server, running IIS on port 80 for local intranet site and the admin website on port 7715. I have checked the host head...

Tasks with no due date at the bottom?
Is there any way to convince Outlook 2000 to show tasks with no due date at the bottom of the task list -- when the sort is on by due date, ascending? I know if it's sorted by due date descending, the no-due-date tasks will be at the bottom, but that's just not it; what I need is a view sorted by due date, ascending, with no-due-date tasks at the bottom. I suppose it seems quite logical: the closes due dates on top, the farthest due dates lower down the list, the ones without due dates at the very bottom. I would love to do the same with Outlook 2003. I think the answer is that...