format numbers

J

Joanne

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
 
R

RichardSchollar

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
 
J

Joanne

RichardSchollar wrote:

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
 
R

Roger Govier

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.


--
Regards

Roger Govier


Joanne said:
RichardSchollar wrote:

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

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
Click to expand...
Click to expand...
 
J

Joanne

Roger Govier wrote:

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
 
J

Joanne

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
 
R

RichardSchollar

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
 
R

Roger Govier

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

Joanne

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
 
Top