 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006format numbers
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this information, and why do you think it should be representative of minutes/hours? Richard > I have a list of audio books in an excel 2003 spreadsheet. > One column should show the duration of the book in this format: [quoted text clipped - 9 lines] > Thanks for your help > Joanne Here is the info in it's original format as seen on line "Just Wait Till You Have Children of Your Own!" (Audiobook) Unabridged. by Bombeck, Erma.; Keane, Bil Publication: [Boulder, Colo Recorded Books, 2005. Product ID: 135192 ISBN: 0788749110 Duration: 2 Hours 21 Minutes File Size: CD: 33MB; Radio: 4MB It gets downloaded as a .csv file and opens in msexcel 2003 on my computer. Here are the column titles and the first record of the set. Title Just Wait Till You Have Children of Your Own!" {Unabridged.}" Author Bombeck, Emma Publication Boulder, Colo Recorded Books, 2005. Product ID 135192 ISBN 7.89E+08 Duration 8491 file Size - CD 34705733 File Size - Radio 4933817 Subject/Genre Fiction The info, when downloaded, comes as a .csv file and opens in msexcel. The column headers are Publication, Product ID, ISBN, Duration and File Size. It is under the Column title 'Duration' that the 4 or 5 digit number shows up in the field instead of the actual duration of the audio book, such as, in this case, 2 Hours 21 Minutes. Actually the fields for ISBN, File Size CD and File Size Radio all have the same problem, yet Product ID comes the same in both formats so must be a simle text number (or so I assume). I have no clue why excel is translating this info to a 4/5 digit number. I was hoping that you mvps might have some idea how to show this data in the spreadsheet as hours and minutes. Thanks for you interest in my question. Joanne > Joanne > [quoted text clipped - 18 lines] > > Thanks for your help > > Joanne Hi Joanne Are you sure that the original time is 2 hours 21 mins and not 2 hours and 2 mins? It may be that somehow, the leading decimals have been dropped and your 8491 should be 0.08491. If that were the case, then a serial number of 0.08491 when formatted as h:mm returns 02:02. That is the only logic I can see in the number that is being returned. If that is the case, then in you spreadsheet, use a spare column to divide your 8491 by 10000, and Format>Cells>Custom>h:mm Copy this new column and Paste Special>Values over the original data. The new column can then be discarded. With regard to the File sizes, they are being expressed in bytes. Again using a spare column, if you use =INT(G2/1024^2) you will get the result of 33 and similarly =INT(H2/1024^2) will give 4 (the results being MB in each case). Again, copy and Paste special over the original data. If you renamed the original .csv file as a .txt file before trying to import into Excel, the Data to Text wizard would be invoked. If you then choose the option Delimited>Next> then choose comma as the separator, it will parse the data for you, but you would have the option of defining the format for the incoming columns. If you defined your ISBN column as Text, then you will get 0788749110 rather than the scientific notation of that number. This is purely a guess as to your time problem, but it is the only thing I can think of that might fit what you are seeing.  SignatureRegards Roger Govier > Here is the info in it's original format as seen on line > [quoted text clipped - 86 lines] >> > Thanks for your help >> > Joanne Thank you so much for your explanations Roger. I thought it might be something along those lines, but don't have enough excel knowledge to ferret it out. Will play with the hrs and mins and see what happens Have a great holiday Joanne > Hi Joanne > [quoted text clipped - 27 lines] > This is purely a guess as to your time problem, but it is the only thing > I can think of that might fit what you are seeing. Roger I tried your suggestion for the number that represents hours:minutes. I don't think that is the answer to this puzzle because the number 31880 returns the numer 4:30 and 29602 returns 23:02. Pretty wierd Joanne > Thank you so much for your explanations Roger. > I thought it might be something along those lines, but don't have enough [quoted text clipped - 35 lines] > > This is purely a guess as to your time problem, but it is the only thing > > I can think of that might fit what you are seeing. Joanne I wonder if that number is the number of seconds of audio time - 8491 would thus give a play time of over 141 minutes ie approx 2 hours 21 minutes plus a few seconds. This being the case, you'd need to divide this number by 86400 (number of seconds in a day) and then format the result as hh:mm ie so: =A1/86400 and format this cell for mm:hh. Please let me know if this looks right to you. Best regards Richard > Roger > I tried your suggestion for the number that represents hours:minutes. [quoted text clipped - 45 lines] > > > This is purely a guess as to your time problem, but it is the only thing > > > I can think of that might fit what you are seeing. Richard You are right on - I checked several of the times on the books and they all are exactly as the calculation you gave me shows them to be. Good job, Sherlock! Thanks for solving my little puzzle. It's always fun to learn a bit of new stuff. Joanne > Joanne > [quoted text clipped - 64 lines] > > > > This is purely a guess as to your time problem, but it is the only thing > > > > I can think of that might fit what you are seeing. Hi Joanne That's strange, because I get 07:39 and 07:06 respectively from those values. However, I do believe that Richard has got it right in his later posting suggesting that the values are in seconds. That would produce results of 08:51 and 08:13 respectively. SignatureRegards Roger Govier > Roger > I tried your suggestion for the number that represents hours:minutes. [quoted text clipped - 58 lines] >> > thing >> > I can think of that might fit what you are seeing.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.