Importing MM:SS From Tab Delimited File and Charting

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

Have I provided enough information to receive support for my issue?

"Bryan" wrote:

> 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/
_______

Bryan wrote:

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

Thanks John. I look forward to trying this out on Monday morning.

Regards,
Bryan

"Bryan" wrote:

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