CODEWITHSUNDEEP
vbaexceltime-series
Hugh_Kelley
Hugh_Kelley

Reputation: 1040

Munging Time Series in Excel

I want to sort data by the date from latest to earliest. My trouble is that the data i have has dates in mm-dd-yyyy text format. I could easily clean this up using Pandas in python but don't know the tools available in excel. Even when I try to change the format by hand it does not remove the hyphens.

I believe that due to the poor formatting when I sort on the data it sorts on the month first and years end up grouped together instead of keeping the data chronological.

Something this basic must be an easy fix but I haven't found a tool to use.

Thanks in advance!

Solved using 

Selection.TextToColumns Destination:=Range("A5"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="-", FieldInfo:=Array(Array(1, 3), Array(2, 1), Array(3, 1)), _
    TrailingMinusNumbers:=True

Which eliminates the hyphen by identifying it as the delimited and breaking each date segment into its own cell.

Very sorry for using \selection\ which I know people hate.

Upvotes: 1

Views: 578

Answers (5)

teylyn
teylyn

Reputation: 35915

You don't need any code to change the text that looks like dates into real dates.

Select the column of dates, then click Data > Text To Columns > Next > Next

In this dialog select Date as the data type and choose the order of MDY if the text shows in month/day/year order. Then hit Finish. All cells will now contain real dates and they can be sorted chronologically.

Upvotes: 1

user4691433
user4691433

Reputation:

I was running into a similar problem a few days ago. Excel would not accept my command to format a cell as date. I could not find the exact site that I used at that time, but this person is having an identical problem. He mentions two methods in passing, one of which worked for me, and there is a third that is the accepted answer there:

http://www.computing.net/answers/office/excel-will-not-change-a-cell-format-/8764.html

To summarize:

  • Edit (F2 key while your cell is highlighted) one of your cells with dates in it and make sure there are no spaces or invisible characters
  • If that doesn't work, then set the next column to multiply your date column by 1, and then try converting the next column to Date format.

A frustrating solution, I agree.

If all else fails you could try the CDate() function in VBA, since you tagged this question with VBA, but that shouldn't be necessary.

Upvotes: 1

Jake Griffin
Jake Griffin

Reputation: 2074

Do all of the cells refuse to change formatting when you change the date format to mm/dd/yyyy or just some of them? Click on one of the cells that refuses to change and look in the formula bar. If you see something like '01-01-2015, the ' is the culprit here. That tells excel to treat this cell as plain text. If that were in cell A1, try =DATEVALUE(A1) in another cell to convert it to a date instead of a string, and you should be able to format that cell however you wish.

Upvotes: 1

dartdog
dartdog

Reputation: 10862

Ah so you want to do it entirely in Excel, make a new column for each of mm dd and yyyy using vba and date parsing so your dates are actually stored by Excel in its' own internal format so if 2011-12-12 is in cell A1 =DAY(A1) will give you just the day part in a new column but as I recall I thought Excel was smart enough to sort dates without that (since it actually has the date as a serial # internally), but there you go there is also of course a month and year function... Check the function list under dates...

Upvotes: 1

WilliamSF
WilliamSF

Reputation: 284

You can parse the dates and sort them in the following way:

from datetime import datetime
l=["2012-10-09","2011-12-12","2012-10-08","2010-03-03"]
dates=[]
for v in l :
    dates.append(datetime.strptime(v, "%Y-%m-%d" ))
dates.sort()

Upvotes: 1

Related Questions