How to sort DateTime stamp down to milliseconds

[TT2013]

Hi,

I am working on the stock trades I download from my broker for my tax. As I fed the records to a tool that calculates schedule D, wash sales, gain/loss etc., I found the time order of the records are very important to ensure correct calculations, but the download from the broker was not in time order. Sigh ..

However, the records come with a datetimestamp in one column down to milliseconds. e.g. 2/17/2012 13:05:48:287

I used =left(A2,search(" ", a2,1)) and right(A2,len(a2)-search(" ", a2, 1)) to split the column into 2, one with the date, the other with the time.

What do I need to do to sort the records by date and then by the timestamp?


I tried

1) sort the datetimestamp column from A-Z as text without split, it is not in time order
2) after the split, I format the date column and then sort it, it won't give me an option to sort it from oldest to newest, just from A-Z
3) after the split, I tried to custom format the time part with the milliseconds, and then sort it, but again, only from A-Z

I am new to Excel so any help would be appreciated.

Regards,

TT

 

[Piktro]

It seems like it may be an issue with the formatting and the way excel handles milliseconds.

I was able to make it work with the following custom format

m/d/yy h:mm:ss.000;@

Making your previous example data read as .287 instead of :287

Using your example data, with 15 or so other imaginary numbers (switched up the seconds, milliseconds, hours, and date), I was able to sort it perfectly once the colon was replaced by a period.

 

[TT2013]

I tried m/d/yy h:mm:ss:000 as well as the one you suggested.

The Oct, Nov records end up on top, then Feb, Apr, Within the month, the 8/20/2012 would be on top of the 8/6/2012.. or 9/13 ahead of 9/7 etc.

It seems the longer the datetimestamp field, the lower value in A-Z order when sorted. I was sorting it in A-Z

Is there any way to attach an excel or csv file up here? I tried table but it wouldn't take the paste from excel properly.

e.g.

2/17/2012 10:00:39:430

2/3/2012 12:05:03:287

2/3/2012 12:05:48:000

2/3/2012 12:06:07:000

2/3/2012 12:08:47:470

2/3/2012 12:08:47:470

2/3/2012 12:08:47:470

2/3/2012 12:25:10:997

<colgroup><col></colgroup><tbody>
</tbody>

 

[TT2013]

I am able to sort it finally by accident.

First, I sort it by Date column, then by DateTimeStamp, it solves the problem of longer DateTimeStamp having the lower value in A-Z.

Can anyone explain why this works?

Excel seems to work in mysterious ways. It doesn't work the way I or others think it does - not sure it is a good thing for a program.