Question on sorting dates

After I download data from a database, the data in the date column is in date 
format, as for example, "11/02/2004". To make sure they are in date format, I 
format them again. Then I tried to sort the date in an ascending order, it 
doesn't work. It did some sorting work, but it put November dates right after 
January dates, and then Feb, Mar.... I guess Excel doesn't really take this 
column of data as dates. 

Any help is appreciated.

Jason
0
User (142)
1/20/2005 8:21:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
469 Views

Similar Articles

[PageSpeed] 38

What happens if you test with a formula

=ISTEXT(A2)

if it returns TRUE the dates are text, if so, copy an empty cell, select the 
dates
and do edit>paste special and select add, then reformat the dates as mm/dd/yyy
or dd/mm/yyyy depending on whether dates are US or UK format
If that does nolt help you probably have trailing or leading spaces as well, 
then you can first do an edit>replace and replace a space with nothing

Regards,

Peo Sjoblom

"Excel heavy user" wrote:

> After I download data from a database, the data in the date column is in date 
> format, as for example, "11/02/2004". To make sure they are in date format, I 
> format them again. Then I tried to sort the date in an ascending order, it 
> doesn't work. It did some sorting work, but it put November dates right after 
> January dates, and then Feb, Mar.... I guess Excel doesn't really take this 
> column of data as dates. 
> 
> Any help is appreciated.
> 
> Jason
0
PeoSjoblom (789)
1/20/2005 8:37:04 PM
Thanks a lot.

Jason

"Peo Sjoblom" wrote:

> What happens if you test with a formula
> 
> =ISTEXT(A2)
> 
> if it returns TRUE the dates are text, if so, copy an empty cell, select the 
> dates
> and do edit>paste special and select add, then reformat the dates as mm/dd/yyy
> or dd/mm/yyyy depending on whether dates are US or UK format
> If that does nolt help you probably have trailing or leading spaces as well, 
> then you can first do an edit>replace and replace a space with nothing
> 
> Regards,
> 
> Peo Sjoblom
> 
> "Excel heavy user" wrote:
> 
> > After I download data from a database, the data in the date column is in date 
> > format, as for example, "11/02/2004". To make sure they are in date format, I 
> > format them again. Then I tried to sort the date in an ascending order, it 
> > doesn't work. It did some sorting work, but it put November dates right after 
> > January dates, and then Feb, Mar.... I guess Excel doesn't really take this 
> > column of data as dates. 
> > 
> > Any help is appreciated.
> > 
> > Jason
0
1/20/2005 9:57:01 PM
>-----Original Message-----
>After I download data from a database, the data in the 
date column is in date 
>format, as for example, "11/02/2004". To make sure they 
are in date format, I 
>format them again. Then I tried to sort the date in an 
ascending order, it 
>doesn't work. It did some sorting work, but it put 
November dates right after 
>January dates, and then Feb, Mar.... I guess Excel 
doesn't really take this 
>column of data as dates. 
>
>Any help is appreciated.
>
>Jason
>.
When formatting your cells in Excel, try the GENERAL 
format selection and see if that helps. Mine sorts 
correctly when set that way and using your example.  Greg
0
anonymous (74722)
1/21/2005 5:12:39 PM
Reply:

Similar Artilces:

The ability to change multiple activity due dates at once
I would find it extremely helpful when I have multiple activities that are due on the same date and I want to change the due date to be able to change them all at once rather than having to open each one separately and then changing the due date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&...

Macro Button Question
Heya All, I am trying to insert and delete rows using macros. Ive assigned the Macro to a button, but would like to be able to insert (and delete) exactly where the button is on the worksheet, no matter where the activecell is.. So, I guess my question is, is it possible to get the cell reference behind a graphic button? Or, even better, assign a macro to a cell? ta, Maria Maria There are "insert row" and "insert column" buttons available from Tools>Customize>Commands>Insert Also "delete row" and "delete column" buttons available from To...

Alphabetical Sorting
How can I sort a group of names alphabetically in a text box? Publisher doesn't have the capability to sort. You could create a table in Word, sort the names, then copy paste into Publisher. -- Don - Publisher 2000� Vancouver, USA "skw" <skw@discussions.microsoft.com> wrote in message news:7FB148DD-FA30-4DDC-B4A8-2D1F2438AED1@microsoft.com... > How can I sort a group of names alphabetically in a text box? ...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Receivable Document Date
I noticed for a past transaction that a receivable was post dated while the posting date was the correct date. What is the best way to correct this? Can I do this through the database? Lynn, The best way to do this is to void the original transaction and re- enter it. This will provide a complete audit trail. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com What is your Posting Setup? Is it "Post To GL" or "Post Thru' GL"? In case of "Post To GL", it is possible as the posting of Receivable could be in a past date and t...

Dating 6 events for 5 groups on a 18 month cycle
I have been trying tocalendarise 6 key dates in a cycle for 5 groups Each group A,B,and C all have different start dates to their cycle in the 18 month period - after that time it all repeats itself. Is there anyway this can be done I also need to find some way of labelling these key dates - Thanks no Denise - Despite Colin's certainty that it can't be done, I suspect all it requires is a better statement of your problem. Are you talking about a Gantt chart? If so, check out some of the links on this web page: http://peltiertech.com/Excel/Charts/GanttLinks.html - Jon ------- ...

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".
how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am working on a program that generates certificates. i want to use dates such as "3rd of April 2010". when i run my program the words that appears onthe screen was 3-Apr-10. can you help me solve this problem? On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote: > how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am > working on a program that generates certificates. i want to use dates such > as &q...

Report filter question
Hi, I've done filters many times before, but something is not right and I can not see it. My report is running of a query which has NO selection criteria in it. It is just a logical view of a table. I have the following code in my report (it is hard coded only because I'm trying to debug the code": Me.Filter = "(((tblMailingList.MemType)='MT-S'))" Me.FilterOn = True I know that the filter code is correct because I went into design query and put MT-S for the Criteria in the field MemType and view the resulting SQL cod...

start and end date of given date
Hi All, What's the easiest way to get the start and end date for a given date. The Mon thru Sun dates. Thanks, rodchar You can use known Monday and Sunday dates and calculate based on current date (or any parameter date): -- '20010101' is known Monday -- '20010107' is known Sunday SELECT DATEADD(DAY, (DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP) / 7) * 7, '20010101') AS monday_for_date, DATEADD(DAY, (DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP) / 7) * 7, '20010107') AS sunday_for_date; -- Plamen Ratchev http://www.SQ...

Help needed on date()
Can anybody please tell me if, and how, I can use the date() funtion in excel for dates lying before 1900. The problem is that I'm trying to fill up columns with dates from a 19th century journal. thanks in advance D. Gerling Take a look at the following: http://j-walk.com/ss/excel/files/xdate.htm MRO "Delano Gerling" <delano@gerling.demon.nl> wrote in message news:vm99477u8k7mdc@corp.supernews.com... > Can anybody please tell me if, and how, I can use the date() funtion in > excel for dates lying before 1900. > > The problem is that I'm trying to f...

hwnd question
hwnd hA was created in process A hwnd hB was created in process B Q: is it legal for A to parent B ? Yes it is legal. But keep in mind these points. One thing to remember is that the child window sends message back to it parent. When your child window sends a message to it's parent window, the thread that the child window is in will block until the message is processed by the parent in a different thread. And be careful to destroy the thread with the child window before you destroy the parent window! Ali R. "anon" <someone@internet.com> wrote in message news:Oz3%23...

Request between week ending date
I am currently using a query that request a "start date" and an "end date", I would like to change the request to "Week ending date" and include information 7 day back. What would I have to change to get this information? In the Criteria row under your date field, you currently have: Between [start date] And [end date] Try: Between [week ending date] - 6 And [week ending date] It is always a good idea to declare date parameters. In query design view, choose Parameters on the Query menu. Enter into the dialog: [week ending date] Date...

Excel doesn't remember custom date format
My web site uses a txt delimited file created by excel. The format of the date column needs to be in the form of YYYY-MM-DD. If I set up a column this way, save it and test my web site, it works. But if I quit Excel, reopen that project and add another entry, the date reverts to the MM-DD-YYYY format and my site no longer understands it. Is there a setting to force Excel to remember the custom format? Using Excel 2000. Jack Jack, If by "reopen the project" you mean that you're opening a text file, Excel will use the date format that it wants, not what you want. Tell the r...

Several questions
I all of a sudden am using Excel again, this time Excel 7 and forgot how to do some things. 1.) How can I expand a page to fill the printer print area? 2.) How can I print all the worksheets on a single document? 3.) Or page 1 of each worksheet? TIA, GWB 1) You cannot expand a print area to fill a paper page. You can shrink it to fit a specified number of pages. Open Page Layout tab and launch (click on the arrow) the Scale to Fit group. 2) Office button (colourful ball) / Print / Print and in the Print What area (lower left corner of dialog)check the Entire Workbook box. 3) Off...

KBV- vlookup question
In the vlookup formula, the range entered includes the columns that need to be searched for matching or similar values and the range also includes the column from which the corresponding value has to be returned. What if there are 2 or more columns within that range that match the lookup value? How does excel handle that? According to Help for Excel 2003, VLOOKUP "searches for a value in the first column of a table array." It only searches in that column for the lookup_value argument. -Ryan KBV wrote: > In the vlookup formula, the range entered includes the columns that ne...

"ghost or blank" messages with date field of "none".....help?
using the ESEUTIL utility, we recently had to rebuild our message store because of a corrupted restore attempt. everything restored nicely...execpt now we have some users that have what seems to be blank messages. when outlook is opened, and messages are listed in the inbox or deleted items folder, these messages are blank except for the word "none" in the date field. they can not be accessed nor deleted. any ideas on how to resolve this issue?? thanks! On 26 Sep 2006 12:07:04 -0700, "kamor56" <kmorgan56@gmail.com> wrote: >using the ESEUTIL utility, we recentl...

Text Formula Question
In COL A, I have a list of 2 and 3 digit charactors. I would like to seperate each string into the three adjacent columns. KQ AJs TT 89s KQ K Q AJs A J s TT T T 89s 8 9 s I know that I can use the Left function for the first col, but cant figure out how to get just the middle or third letters. Thanks in advance, Andrew Check your last post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------...

Date format in search?
Despite having changed the regional settings for each site collection to English(United Kingdom), using Advanced search and the Last Modified Date still only works if US date format. I.E. to search for files changed today I have to enter the date as 4/9/2010 and not 9/4/2010 as expected. Is there something else I need to configure to make the search use British date formats? Changing the regional settings there only effects the display. try changing the settings at the web application level in Central Administration. If that doesn't work, you'll need to modify the ser...

Date Error
Hello, Any help on the following matter would be greatly appreciated... I recently upgraded from Windows 95 to Windows 98 (yes, I realize the year is now 2005) and when I tried accessing one of my Excel spreadsheets, the dates were all whacky... For instance, what used to show as 05-Feb-2005 now appeared as "05<>Feb<>2005" (where <> is actually a small square). When I input a new date, it treats the entry as text as opposed to a date. This is even though I format the cell to recognize the entry as a date. Has anyone experience this before? I've been able...

Photos not moving with rows when sorted!
Excel 2000. A worksheet with names of people, and various data about them. Each row is a record--the person's first name, last name, and various data, each column is a field. One column is labeled "Photo", and has a small photo of the person in it. I notice that when I sort the rows, by data in one of the columns, such as "first name", "last name", etc., the photos do not sort correctly with their rows. Why? Do photos just remember their place on the page, their cell position (E7 or whatever), and not the row they are in, are not considered part of the row...

CSplitterWnd questions,
When my program starts, only one view of the four splitter window shows. I have to manually drag the bars to induce the other 3. My question is: how do I readjust the size of each splitter window in code? Also, what method do I use to get a HWND (or CWnd) to each subdivided window? vsgdp wrote: > When my program starts, only one view of the four splitter window shows. I > have to manually drag the bars to induce the other 3. My question is: how > do I readjust the size of each splitter window in code? Also, what method > do I use to get a HWND (or CWnd) to each subdi...

Completion Percentage of a date range
I have started a task sheet in excel and i have a "task description" coloumn, a "start date", "finish date", "number of days" and a "% complete" column. What i want to achieve is the user to enter the first task start date and the number of days it will take to complete. Then excel will work out the end date and the percent complete field relating to the current date. Is there a easy formula of working out the percentage? Many thanks Hi Brian, I think this is right? Assume Column A is Task, B is Start Date, C is No. of Days, D is End ...

Money 2003 Question #2
My computer froze and shut down with Microsoft money open. When I reboot and tried to open file it gives me an error "Money has detected that you did not close the file before exiting. Money now needs to verify the information in your file. Chose Yes. When I chose Yes, it says "repairing file" and then gives me the error that the password I entered was incorrect. Can anyone tell me how to prevent this situation? It's happened several times and takes me a long time to update my file from a back up version. You might want to try renaming the client.xml file. I...

wasy macro question
I used the macro recorder to get the code to resize columns on a worksheet Cells.Select Range("A1766").Activate Cells.EntireColumn.AutoFit What I don't understand in the Range. It's seems to be referring to a single cell. Could someone explain to a VBA newbie? I'm importing a text file and I want to resize the columns after import. I have the import working just need this one last thing. The worksheet will always have the same number of columns but the number of rows may vary will the above code still work? gls858 gls858 wrote: > I used the macro reco...

Money 2006 small business sorting by customers name
Is there a way to sort customer name by their last name? If I enter name last name first, the invoices are printed last name first. So, if I enter name first name first one cannot find transactions alphabetically. Any suggestions? -- Christopher J. Fazio, V.M.D. Hope, New Jersey ...