[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel seems to be treating a date and time as text when sorting

Posted on 2012-09-02
15
Medium Priority
?
488 Views
Last Modified: 2012-09-02
I have two files. They are outputs of two different processes. One treats the date as a date when I try to do a sort in Excel (it says "oldest to newest") but the other Excel treats as text (from A to Z). I want it to treat it as a date but I cannot manually reformat or even do it using VBA. I have to tell the person who designed the process what is wrong. Any suggestions on what is happening here.
Both are attached.
treatsDateAsDate.csv
treatsDateAsText.csv
0
Comment
Question by:willie108
  • 8
  • 7
15 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358541
The format of the date is different in your files:

Working: 979931706,8/18/2012 9:08,...
Not working: 6928 5800708,31/10/2009 12:12:55,...

The working one has the date in the format month/day/year. The not working one is in the format day/month/year. Depending on your system settings one will work, the other not. In your case you have the month first, so I assume you use US settings. Just request the second non-working file to be in the correct format (month first) and it should work.
0
 

Author Comment

by:willie108
ID: 38358550
Thanks. I did try reformatting manually just to see if that makes a difference but it did not. Does this still agree with your conclusion?
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358557
What do you mean by "reformatting"? If it is already in Excel and you try formatting the cell, this will not work because the format only works when Excel already knows it's a date. You need to manually change the text file with a text editor like Notepad++ and then open it in Excel.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:willie108
ID: 38358569
I see. If I want to change it with Notepad++ would that mean changing the order of the month day and year for each row?
Thanks.
0
 
LVL 20

Accepted Solution

by:
TheAvenger earned 2000 total points
ID: 38358573
Yes, unfortunately. You can do that manually for a one-time test but if need it on regular basis you need to either request the file in the correct format or make a converting application
0
 

Author Closing Comment

by:willie108
ID: 38358574
Thank you.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358576
Oh, you can try this: rename the file to have an extension .txt (instead of .csv). Open Excel and press Ctrl+O (open file). Select all files as file type and choose your file. Excel will ask you how columns are separated. Select separator and select the comma. Then it will show you all columns. Select the one with the date and time and there will be a drop down for the order of the date. MDY means Month/Day/Year while DMY means Day/Month/Year. Select DMY for the not working file and open it. It shall than have the values correctly.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358579
Ah, still doesn't work because of the time :-(
0
 

Author Comment

by:willie108
ID: 38358583
Thanks for the extra idea.
0
 

Author Comment

by:willie108
ID: 38358584
Oh one other thing. If I change it to day/month/year as you suggested originally, will the fact that the time is there cause any interference?
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358587
Yes, it does unfortunately. You can split the columns both by comma and by space but this will break all other columns as well.

I don't think there is an easy way to do that.
0
 

Author Comment

by:willie108
ID: 38358589
I am not sure I understand. One of the files I sent treated it as a date in spite of the fact that there is a time in there as well. In the problem file, if I change as you said to day/month/year, then won't Excel treat in like a date in spite of the fact that it too has a time in it?
0
 

Author Comment

by:willie108
ID: 38358595
Hi. I just tried to change the date part to month/day/year plus time and it seems to work. It treats it as a date.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358597
If you change it as I said, Excel expects to see a column with date only. However your column is date AND time. So it tries to interpret it as date and fails (the time remains), gives up and writes it as plain text. If it was only date, it would work. What you can do is add manually comma between the date and the time in the text file and then open it in Excel as I described but you will have the date and the time in two separate columns.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358598
Nice!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question