Extrating Milliseconds from time

[ballz]

I have a CSV file which has time in a format of hh:mm:ss.milli.

I was using Right(CELL, 3) which takes the last 3 digits which is the millisecond. This was working fine until I got some new data sets.

In this new data set, When I take Right(CELL,3), its suddenly giving me the last 3 digits of the excel time format converted (which goes from 0-1 in a day).

Do I have to do some formatting? I don't understand why it wont just give me the last 3 digits of my hh:mm:ss.milli, and is instead converting it to the 0-1 value and giving me the last 3 of those.

Thanks for any help.

[Special-K]

Can you give examples of data where this works and does not work?

[BadlySpelledBuoy]

Could you post some of the data so we can see it?

BSB.

[ballz]

do you guys mean upload the files? I can, but don't know how.

Otherwise, they are all .CSV files, with data that looks like

1:20:00.057,7981,1
1:20:00.059,7981,1

When I open the .CSV file, the first column (time) will be automatically cut off and look like 20:00.1. With the fx box showing 1:20:00 AM. Like it auto formatted it or something, and it cut off the hour digit.

I had to then goto format cell, and format it to custom hh:mm:ss.000

My other .csv files that DID work, did not auto format it to 20:00.1. It had the full value just as the .CSV did.

I'm confused as why they would auto format my .CSV file. Unless I changed some excel parameters?

Thanks guys

[MrShorty]

To attach a file, click "go advance" then click on "manage attachments".

I expect that what is happening is that sometimes Excel is recognizing your time stamps as times and converting them to serial numbers (see here http://www.cpearson.com/excel/datetime.htm for a quick explanation of how excel treats dates and times) and other times it is not and is leaving those times as text strings. I don't know what is the best solution, but I would try to get excel to treat these the same way (either as serial numbers or as text strings) every time, consistently.

Perhaps the first step in this is trying to identify what is different between those times that it is declaring serial numbers and which ones it is declaring text strings. The two examples you give are essentially the same and are both converted to serial numbers by default. Can you post examples of those time stamps that are not converted to serial numbers?

You haven't said where these CSV files are coming from. In comparing the differences, and if you have any control over the source of these time stamps, I would be first tempted to make changes at the files' source to attempt to make the format more consistent.

If you cannot control this at the file creation step, then it will probably be easiest to control this at the file importation step. You don't describe how you are importing the files into Excel, so I will assume you are using the File-Open command. On a .CSV file, Excel automatically opens and interprets the fields based on its own default thinking. When you don't like the way Excel interprets fields, then you need to somehow import the file through the text import wizard. You can do this by either using the Import External Data -- From text command (Data Group), or by saving the file/renaming the file with the .txt extension. At the appropriate step in the text import wizard you can specify that this column should be imported as text (since you seem more comfortable with the text manipulation function).

[ballz]

Ok I've uploaded the attachments.

They are both .CSV files.

They look exactly the same to me when I open them up in notepad.

[MrShorty]

I'm not sure why they look exactly the same to you. When I open these CSV files in a text editor, I immediately notice that the K4 file is using a colon : to separate seconds from fractional seconds/milliseconds. The A4 file is using a decimal point to 'separate' seconds from fractional seconds/milliseconds. When Excel opens the A4 file, it recognizes the time stamp by default and converts it to a date/time serial number. Excel cannot recognize the K4 entry as time, so it treats it as text by default.

As I suggested, my first choice would be to go to the program/source for these CSV files and see if I could do something that would make them consistent. If that is not an option, then you will need to do something before importation or during importation to get Excel to read these things consistently.

[ChemistB]

=IF(ISTEXT(A1), Right(A1,3)+0, MOD(A1, 1/(24*60*60))*24*60*60)
Does that work for you?

[ballz]

Wow MrShorty, you are correct.

I will have to ask to see why the Data I pulled is in different formats. Don't think I would have ever noticed that... I'm a moron.

Thanks