One of the first things we learned about computing was "If you can't find
the answer, change the question!"
What that means is: in computing your question needs to be literally correct
or the computer can't answer it.
In your case, you are asking the computer to convert a "something" that is
not a time. The previous answers on this topic would have worked fine to
convert a "Time". But chances are, that's not what you have in those cells.
So the suggestions "work", but they are not the answer to YOUR question
Excel stores "time" as a "decimal fraction of a day". The part to the LEFT
of the decimal is "The number of days since January 2, 1904" on the Mac, and
the number to the right of the decimal is "the fraction of the day between
midnight and this time".
To perform computation on those numbers, you must first convert them into
Excel times. Sadly, the brick wall you hit was that the Excel time function
will not produce precision greater than tenths of a second. Which means we
have to convert those times in seconds and decimals to "decimal fractions of
a day" the hard way.
Of course, in other versions of Excel, we can leap in with a little VBA
function that does this easily and without a lot of thought or effort. But
in Excel 2008, we have to do it the really hard way
Add a column beside the column that shows 53.23 sec and type in it
=C3/(24*60*60)
Assuming the value in C3 is a number of seconds and decimals of a second,
that will produce a number that will format as a Time in Excel. If you give
the result cell a custom format of "mm:ss.000" you will see
63.646400 seconds represented as 01:03.646
The 423.04 is a similar challenge. Again, we have to "roll our own" time,
because Excel's Time function does not offer the precision we need.
Assume that Cell C3 contains "462.5300". Note that I have expanded the
number format to show four decimal places, so we can make sure we do not
drop any due to rounding errors.
Here's the formula:
=TIME(0,VALUE(LEFT($C3,1)),0)+TIME(0,0,MID($C3,2,2))+IF(ISERR(VALUE(RIGHT($C
3,LEN(C3)-3))),0,VALUE(RIGHT($C3,LEN(C3)-3))/(24*60*60))
Simple? Let's parse it out
=TIME(0,VALUE(LEFT($C3,1)),0)
This bit gets the left-most character of the string (the "4" in 423.04) and
converts it to an Excel Time value. We don't need the precision here,
because we can never have more than 60 minutes.
+TIME(0,0,MID($C3,2,2))
This bit grabs the two seconds digits and adds them into the mix, as
seconds.
+IF(ISERR(VALUE(RIGHT($C3,LEN(C3)-3))),0,
This bit checks for an error if we attempt to get a value from the string to
the right of the decimal point. If we do get an error, chances are that
string contains "0000" so we simply set the value to 0, otherwise...
VALUE(RIGHT($C3,LEN(C3)-3))/(24*60*60))
We grab the characters to the right of the decimal point, convert them to
hundredths of seconds, and simply add them to the Time value we are
building.
That will do it
Cheers
Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel
I have a large spreadsheet with elapsed time records shown as 53.23 sec, and
others as 423.04, meaning 4' 23.04 sec, and need to perform calculations on
these. I have tried formatting with the time formats, but none give me results
that make any sense for this use. For example, I would like to know what
percentage of 53 sec is 4 min 23 sec. The prior suggestions on this topic have
not worked.
This email is my business email -- Please do not email me about forum
matters unless you intend to pay!
--
John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:
[email protected]