Formats: Should be Duration MM:SS, but is really Time (12:30:00 AM)

[Tbone]

I'm working with a large piece of Data where the description for th
Duration piece is that is should be MM:SS, but the numbers are actuall
in a time format.

For example 00:30 is actually 12:30:00 AM.

Is there a way to re-format these numbers so that they are MM:S
without lossing the accuracy of the data. As it is now, if I chang
00:30 to a number it comes out as .02.

Thanks
-Tbon

 

[Fred Smith]

You need to decide whether you will keep your data as Excel Times, or as a
number. Most people would use Excel Times, because they are so flexible. You
can display the time in any format you want (eg mm:ss), and math will work
properly using them.

It's relatively easy to convert a time to a number, but how do you propose
to store both the minutes and the seconds? It won't be easy in one cell. If
you want to store seconds, the formula to convert a time to seconds is
=time*24*60*60. You can then display this number to any precision you want.

Also, your example needs more clarification. 12:30:00 AM with a format of
mm:ss is 30:00, not 00:30 as you stated. The format which will produce 00:30
from this time is hh:mm.

 

[Jon Peltier]

Tbone -

Use a number format without PM. 12:30 AM is half an hour after midnight, or a half
hour elapsed with respect to 0:00:00.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______