Re: format numbers
- From: Joanne <RobJo65@xxxxxxxxxxx>
- Date: Tue, 19 Dec 2006 18:18:33 -0600
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
RichardSchollar wrote:
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
Joanne wrote:
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
Joanne wrote:
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
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.
.
- References:
- format numbers
- From: Joanne
- Re: format numbers
- From: RichardSchollar
- Re: format numbers
- From: Joanne
- Re: format numbers
- From: Roger Govier
- Re: format numbers
- From: Joanne
- Re: format numbers
- From: Joanne
- format numbers
- Prev by Date: Re: Posting a Sheet here
- Next by Date: Re: Visual Basic Question
- Previous by thread: Re: format numbers
- Next by thread: merge 5 excel files
- Index(es):
Relevant Pages
|
