Time formats
Posted by Philip on June 19, 2001 10:44 AM
Hi,
I am opening some .csv files in Excel. One of the fields that it is opening is a field representing a length of time in this format 24:43 (24 minutes and 43 seconds). When the file is opened in Excel it displays totally differently. For instance, a field that has nothing more in it than "19:16" will display as 19:16 but if you click on the cell, it actually says in the edit box, "7:16:00 PM" which makes no sense to me. To make matters worse, occasionally, I will have one of these data records come in as a different format by default. One of them, which in the .csv file is, "42:46" displays as "42:46:00" but if you click on the cell itself, the edit box says that the data in there is really, "1/1/1900 6:46:00 PM" The extra weird part is that if you click in the edit box as if you were going to modify the contents but do not actually make any changes, when you leave the cell, the actual display changes to be, "1/1/00 18:46" Anyone have any good ideas?
Posted by mseyf on June 19, 2001 1:14 PM
Excel is treating the amounts as time of day instead of length of time (which I don't know how to change). You could try to import the column as 'text' instead of 'general'.
what kind of calculations (if any) do you need to perform on this data? Someone else may have a keen idea on how to add up the text times.
-Mark
Posted by Barrie Davidson on June 19, 2001 1:22 PM
Go with mysef's suggestion.....
You could then use calculated fields, one for hours and one for minutes. The formula (assuming your data is in cell A1) would be:
For hours:=VALUE(MID(A1,1,FIND(":",A1)-1))
For minutes:=VALUE(RIGHT(A1,LEN(A1)-FIND(":",A1)))
Barrie
Posted by Richard S on June 19, 2001 8:09 PM
Re: Go with mysef's suggestion.....
If the file is fixed width, could you perhaps split the time column into three, one for hours, one for : and one for minutes, and not import he : column?
Posted by Mark W. on June 20, 2001 4:15 PM
After you open the .CSV file just format these values
as [h]:mm and don't be concerned with how they're
displayed in the formula bar. Elapsed time values
that are greater than 24 hours will always be
displayed in the formula bar as a datetime. It's
showing the 1st day of the year 1900 because you're
worksheet using the 1900 date system. When you
entered edit mode you inadvertantly applied a default
date/time format. 1/1/1900 was displayed in as m/d/yy
and the time component was changed from 12-hour to 24-hour
representation.
