Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Hmm... you could also use TextConverter to perform a conversion. Also, for charting, take a look at TARGET Reports - this might help you out. You would probably still need to use TextConverter to convert your stuff into dates.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|