dates #5

Is there a formula to ad a numeric value to the name of a 
month.  I am running a query in excel (pulling from an 
external source) and would like to sort as teh table 
refreshes by month and year.  I have code to to do the 
refresh and the sort, but it sorts in alphabetical 
order.  I was thinking a formula could place a numeric 
value to the month (1 = Jan, 2 = Feb, etc) and I can sort 
based on that.  Is there an easy formula to do this.

Thanks in advance
0
anonymous (74722)
12/10/2003 1:50:21 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
368 Views

Similar Articles

[PageSpeed] 56

Your easy formula could be:

=MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov"
,"Dec"},FALSE)

Or your could reference a table of month names instead of embedding the
month array.

-- 
Jim Rech
Excel MVP


0
jrrech (1932)
12/10/2003 2:26:51 PM
Reply:

Similar Artilces:

Date/Time Macro
I guess this is part 2 of a query I started below. I need a macro that = will=20 enter both the current date and time into the active cell whenever it is = run.=20 Since this is for a workbook shared across a network, the information = needs=20 to remain unchanged in the cell even if the macro is run for another = active=20 cell by someone else on the network. Help! I've recorded or written very few macros, and unfortunately I = need to=20 have this one running as soon as possible. Thanks in advance for your = help,=20 and thanks to the folks who offered assistance to a related date/tim...

Dates 02-01-08
I am trying to write a query that will return the last four dates a customer has visited me. The database I am working with does not assign trip numbers so all I have to work with are the actual dates of the visits. How can I get it to return the last four trips for all of my customers when each person's actual visit dates will be unique? My mind kept going back to using a date range, but because everyones last four visits are different, that won't work. (For example: Fred visits me four times in one week while Wilma visits me four times in one month. So if I were to query ...

322051 problem on Windows 2000 server and exchange 5.5 Admin not opening
I am having the exact same problem as described in the Knowledge Base Article 322051 http://support.microsoft.com/default.aspx?scid=kb%3Ben-us% 3B322051 However, I am not running NT4, but 2000 server. I obtained the hotfix described in the article, but it doesn't install on a non-NT computer. I have checked the DLLs mentioned in the article, and as far as I can tell they are the 56bit versions (although only one actually says "export version" the others give no indication) I am not exactly sure what caused this. I have inherited this network and cannot speak to the prev...

relative date appearance
I am working on a project tracker in which relative dates are calculated. For example, Column C's formula is =B(row #) +7, indicating that the date in Column C is the date in Column B plus seven days. Because this is a project tracker, information will be added to the spreadsheet as new projects come along. If I drag the formula down so that it will be available for future projects, the cells read 1/0/1900. Is there a way I can have the formula in place for future projects but leave the cell in which it resides blank? Thanks. pbtpbtpbt@gmail.com wrote: > I am working on a project tra...

month-date #2
Hi all, I have '23-Dec' format in a coloumn. How can I sort Month first and then first date first? Thanking you in advance. Regards, Kirandeep Surely the sort will get it in m onth order anyway? -- HTH ------- Bob Phillips "Kirandeep Singh" <kiran_excel@hotmail.com> wrote in message news:e$zFD6NdEHA.3616@TK2MSFTNGP10.phx.gbl... > Hi all, > > I have '23-Dec' format in a coloumn. How can I sort Month first and then > first date first? > > Thanking you in advance. > > Regards, > > Kirandeep > > Hi are these...

Mixed dates
I'm new to Access, and trying to create a chronology. The problem is that some of the dates are '01/01/2005' and some are just 2005, or '01/2005'. It doesn't like these formats. What can I do? Thank you! "2005" isn't, in Access terminology, a "date"... nor is "01/2005". If you wish to use Access' date/time features/functions, you'll need to decide how to convert so-called dates to actual dates. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services me...

Need help formatting dates
I am using Excel to track the Accounts Receivables for a small business. I have added a column to track the # of days an invoice is overdue. For example, the invoice date is 7/1/04 due date 8/1/04 It is 2 days overdue. Tomorrow it will be 3 days etc. Right now I am manually calculating these date. Is there a way to format the column to automatically calculate the number of days overdue? -- Thanks! Cathy Hi Cathy yes assume due date is in cell A1 and number days overdue in cell C1 then in c1 enter =today()-A1 then right mouse click on C1 choose format cells choose number click OK Ch...

How do I enter a date range ie -7 from current date in MS QUERY
I am creating an excel pivot table using external data. I want to include only records that are equal to current date and past 6 days. In otherwords, on any day that I run the pivot table I would bet current day + 6 days prior data. How would I setup the criteria and value cell. I can successfully specify a criteria field and in the value but = date(now()) and this works fine for current days data but I am having trouble using the > date(now()) - 7. Any suggestions? Thanks Hi Personally, I would use Today() rather than Now(), unless you are sensitive to the hour as well. I you...

workflow ability to set task specific due date
I would like to have the ability for workflow rules to create tasks with a specific date, taken from another (custom) field from the workflow's object. For example, I have a workflow rule on an Opportunity that will create a task that ensures say a quote is prepared. I have a "Quote Required By" date field on the opportunity. I'd like workflow to be able to create that task with a due date as specified in the "Quote Required By" date field. My only option is to create it with an empty due date or a delay (days, hours, minutes) from the task creation date. ...

Error message with event id 5
Hi all, I want to know what is the error of event id 5, An unexpected MAPI error occured, error returned was (0x80040154)? Thanks "dwi" <anonymous@discussions.microsoft.com> wrote: >I want to know what is the error of event id 5, An >unexpected MAPI error occured, error returned was >(0x80040154)? You'll have to provide some more information. The 0x80040154 is an unregistered class. What version of Exchange are you running. What O/S are you running? From the Event Log entry: EventID Source Category Type Description This *might* be what you're looki...

formating dates #2
i am new here so forgive me if this is an old or stupid question. i have excel 2000 and i am trying to enter in dates for a check book register. i set the format for the cell to date to xx/xx/xx . when i enter in a date of 010203 for jan 2nd 2003 i get a date of 01/14/55 i can not figure this out. i am sure i am doing something wrong since i do not use this that often. please any help is greatly appreciated thank you mike c Mike, You need to enter the date as 01/02/03, so that Excel will then recognise it as a date. Otherwise it treats it as a serial number, and calculates the date as t...

Wrong date on home page brings up 2 year old investments-can't cha
When I open Money 2007, my date showing on the left side is 2 years off-not exact. I have tried to advance it when I look at my investments page but it says I can't set a date in the future. Can this date be changed to refllect my current totals? I hate to reload everything. I reloaded a backup from a week ago but it didn't correct the problem. Please help, thanks. =?Utf-8?B?VGVk?= <Ted@discussions.microsoft.com> wrote in news:C08B8251-AC6C-42FE-8519-911864B52F27@microsoft.com: > When I open Money 2007, my date showing on the left side is 2 years > off-not exact. I ...

Outlook 2002 Hangs #5
Outlook comes up and my initial screen is my inbox. The PC hangs. I can't get task manager to initial. Title bar in Outlook says Outlook not responding. Running XP Pro with latest updates same for Office. Itried doing a reinstall and same problem exists. Next step is to uninstall Outlook and reinstall. Help I have tried the "Repair" install twice, and complete removal and installation twice. It works at first, but then fails. The same Activity Log entries and behavior. "Bob" <bb4@attbi.com> wrote in message news:0ef001c362c9$e7211780$a001280a@phx.g...

Dates not formatting on Chart
I have some spreadsheets linked to each other and the data is being displayed in some charts. Now, when I look at the dates on the bottom of the chart, it is displaying 39095 and several other numbers. I have formatted all of the of dates to display as mm/dd/yyyy but it is not changing the dates. I have enter in my information again and I am still not having any luck. I really don't want to have to build these charts again. Any help would be appreciated. Thanks When you state: " I have formatted all of the of dates to display as mm/dd/yyyy but it is not changing the dates&quo...

concatenating two fields in one cell, of which one is a date.
I have two cells: Cell A2 Today's date is Cell A3 02/08/2007 I want cell A4 to state: Today's date is 02/08/2007 When I use the formula =CONCATENATE(A2," ",A3) I get the following: Today's date is 17669 How do I make this state [Today's date is 02/08/2007] in one field? One way: =A2&" "&TEXT(A3,"dd/mm/yyy") Or =CONCATENATE(A2," ",TEXT(A3,"dd/mm/yyy")) Regards Trevor "Manuel Murieta" <livelonger@gmail.com> wrote in message news:e0Cvbw8SHHA.480@TK2MSFTNGP02.phx.gbl... >I have ...

Mailbox missing after exchange server 5.5 crashed
Scenario. After exchange server 5.5 crashed, and re-building, some users' mailboxes weren't retrieved. Their IT guys told them their accounts wasn't created as .pst's, and those with pst's could get their mailboxes retrieved. Now on their local machines is only a .dat file - dating to before the server crashed. Can their mail still be retrieved from somewhere? I don't think IT made proper backup, that's why no-one could help them. Please advise? sounds to me like you are correct...if proper backups had been taken, all data could have been restored...pst ...

Use Current Date on text file
I'm have a button on a form that will transfer text from a query to a text file on my c:drive named "testfile05-01-07.txt" I need to transfer text everyday, once a day. I don't want to overwrite the file each time. Instead I want to create a new file each day with the above name but the current date: example testfile05-02-07, then on the next day a new file should be named "testfile05-03-07", etc.. Here's my code to transfer to the text file: DoCmd.TransferText acExportDelim, , "QryExport", "c:\temp\testfile05-01-07. txt", True Please h...

How to sum up the value in column based on a 5 day week period?
Hello, Im trying to add the amount of time spent on a type of machinery based on a 5 day week period. For example from 3/1/2010 to 3/5/2010 not including weekends. On the sheet with the data of the amount of time I need added is layed out like this: Colomn A Colomn B Colomn K Example:3/1/2010 PL 1 1:54 3/1/2010 PL 5 0:13 3/1/2010 PL 1 0:07 3/2/2010 PL 5 0:13 3/2/2010 ...

date time consolidation
I have two separate cells. One with data one with time. I need to make the two cells into one with the date and time. Seems simple but..... The method you use to accomplish this will depend on how the date and time information is stored. Excel can >store< information one way and >present< it in another. An Excel compliant date is in fact a large integer number: today, 10 Feb 2010, is stored as the integer 40219. An Excel compliant time is a fraction of a day: 6:15 pm is stored as a decimal portion of a day, or 0.760417. Test how the data is stored by reformatting the...

Recovering a deleted mailbox on an Exchange 5.5 server
One of our user mailboxes was accidentally deleted and I need to recover it. I have a recovery server set up with sufficient disk space, but the locations for Exchange, the transaction logs, priv.edb and pub.edb will not match the live server beacuse they are spread out over 4 drives on the live server. I don't need to worry about transactionj logs (I think), because the user will be happy with getting his mailbox back as it was at the time of the last full backup. Can anyone give me some guidance as to what to look out for here? Some questions I have: Do I neet to restore the pub...

Long Date / Between and...
Hi all, I just converted from 2003 to 2007 and at the same time I took the opportunity to “clean up” some in tables…I changed [DateCreated] format from short to long date since the Date/Time stamp Is valuable (previous I used one separate date and time) ..Here is the issue. Running the query with the parameters in the [DateCreated] field, Between(First Date) AND (Second Date) does not function.. what am I doing wrong? Thanks! Changing the format of a field doesn't actually change what's stored in the field: it just changes how it's presented. If DateCreate...

recurring anniversary dates
Trying to highlight recurring anniversary date. ex: Hire Date: 02/04/2003 receives new vacation on 02/04/2005. Highlight that date every year to remind to reset vacation dates. Can this be done just using the mm/dd, not the year, And have it stay highlighted until vacation dates reset, then go back to normal? Using MSExcel 2000 Thank You Blackhawk ...

OWA 5.5 Stops responding
Hey I'm having a problem where OWA stops responding after a period of time. I have recently put the OLK2K3 patch on and this is still happening. Any ideas> ...

Calculating new date by entering number of additional workdays
Would love some assistance with the following: If I have a start date in a cell (A1), I am hoping to enter a number of additional workdays into another cell (B1), resulting in a new date -- skipping weekends -- in a third cell (C1). Looking for the formula for C1. If it helps, here's an example: * Start date = 4/1/10 (which is a Thursday) * Additional workdays = 5 * Solution = 4/7/10 (because it skipped the weekend) Thanks in advance. Hi Ensure that you have the Analysis Toolpak loaded. Tools>Addins>Analysis Toolpak then with startdate in A1, number of days t...

Subtract a group of cells from a total based on ending date
I don't know if this has already been discussed or not but I couldn't find anything about it so here's my question: I am working on a spreadsheet that calculates interest owed based on how many months have been paid on a contract. The original spreadsheet was based on a formula that took the date of the last payment and subtracted it from the current date, then calculated the interest on that period of time. Well, that doesn't work if the contract is a 2 year contract from 2001 and is already over. We can't charge more interest than what they were originally quoted,...