Importing MM:SS From Tab Delimited File and Charting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I am importing a tab delimited file as "text" with data in the format of
MM:SS. (I am changing the data type to text in the file import wizard).

A sample of my data is as follows:

46:39
00:10
00:00
17:50
00:00
07:29
15:42
02:17
02:58
31:25
00:00
64:38
06:34
28:24
00:00
26:21

I'm trying to create a column chart with this data, however, rather than
getting a seperate column for each line item I get no columns and a legend
that contains all values listed above. I've tried changing the datatype to
custom mm:ss but end up with the same charting results, nothing.

I'm pretty good with Excel but this one's got me stumped. Any suggestions
would be greatly appreciated.

Regards,
Bryan
 
Bryan -

Do you want to chart the individual times? By telling Excel this is text, it doesn't
know what to plot. So it takes all the text in your selection and uses it in the labels.

Let's convert that mess into real times. Excel's going to choke on mm:ss, because it
likes to see an hour first. Suppose your data's in A1:A16. In B1 enter this formula:

=TIMEVALUE("0:"&A1)

This converts 46:39 into 0:46:39, then into a fraction corresponding to the fraction
of a day represented by 46 minutes and 39 seconds, or 0.0323958333333333. Apply a
custom number format of [mm]:ss, and it will transform into 46:39 and actually mean
the time 46 min 39 sec. Fill down to B16 and you have a column of times.

I tried just importing the data as a date, and the hour thing messed it up. You
could do it a different way, too. Parse the input data so : is a delimiter, so you
get a column of minutes and another of seconds. Then your formula to produce a time
would be:

=TIME(0,A1,B1)

This will give you 12:46 AM, but apply the format as above, and it will be fine.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top