I need to format a column to accept data as elapsed time in the the
format mm:ss. when I try to format a cell using custom and typing in
[mm]:ss, that cell displays data the right way, but it still expects
data in the h:mm:ss format. In other words, if i type in 1:30 to
indicate 1 minute, 30 seconds excel interprets the data as 1 hour, 30
minutes, and rewrites my input as 90:00.

The second, more frustrating, problem occurs when I try to reformat a
column of text data to the [mm]:ss format. I entered the elapsed times
using cells formatted for text, but now I want to change the formatting
to elapsed time. When I change the formatting of the cells to [mm]:ss,
excel again interprets the data as h:mm:ss and overwrites my data,
effectively corrupting the entire column of data. Also, this rewrite
only happens when I double click the cell to edit it. In this way,
excel is able to corrupt my data, but also conceal it so that users may
not realise it until much later when undo is not convenient. After the
data has been corrupted, if I double click a second time, excel
displays the data as a time and date, indicating to me that it
reformatted the cell. Unfortunately the formatting pull-down menu in
the formatting panel doesn't update dynamically to reflect the
formatting applied to a cell as I select it, so I have no idea if the
cell has been reformatted or not.
I need to know how to control how excel interprets the data in a cell
when I type into it, or when I apply a new format to the cell.

Sorry about the long post - any help would be greatly appreciated!


--
jazzsax505
------------------------------------------------------------------------
jazzsax505's Profile: http://www.excelforum.com/member.php...o&userid=23844
View this thread: http://www.excelforum.com/showthread...hreadid=374934