How to get Excel to stop formatting time cells incorrectly

[Guest]

I have a CSV file which I am importing into Excel. it has lines like:

6,Nov-00,Las Vegas,2:23,4
7,Nov-01,Memphis,2:11,7

The second to last column is a time, as in the number of minutes and seconds
it took to do something. When I import into Excel, it treats it as if it is
HH:MM, and adds "SS: AM" onto the end (Well actually, I think it changes the
value to its internal format, because when I try to convert to "General", it
changes
these cells to very large integers). I can get it to store as MM:SS, but
then it wants
to tack on an HH:, which it assumes is 12: (as in AM). But these are NOT
times
of the day, but periods of time, so this does not make any sense.

How do I get it to store them either as just text (without manually typing
them
all in, if possible), or store them as just MM:SS (the latter would be nice
so I
could do math on them), WITHOUT hours or days or anything else--just periods
of time?

Thanks for any help.

 

[Dave Peterson]

Put 60 in a helper cell.
edit|copy

select the range with the time
edit|paste special|check Values and Divide

Format the cell to show MM:SS

(but don't look at the formula bar! Don't worry, be happy <vbg>.)

 

[Peo Sjoblom]

Rename the CSV to txt and open it, then the text import wizard will start
and in step 3 you can set format under column data format after select the
column(s) in question (or open it through data>import external data, select
all file types so you can spot the CSV file and then the text import wizard
will start)

--
Regards,

Peo Sjoblom

(No private emails please)