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.

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.)





Chuck Cusack wrote:

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

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 dataimport 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)


"Chuck Cusack" wrote in message
...
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.