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

What are you doing with your minutes and seconds?
Are you eventually adding them up, and then converting them to hours and/or
days?

If you're *not* converting them, why not "go with the flow", and *USE* hours
and minutes instead?

The display can be made to look identical.
[hh]:mm looks the same as [mm]:ss, doesn't it?
And it sure makes data entry a lot easier.

Just a thought.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jazzsax505" wrote
in message ...

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

The most important thing you need to know is that setting the *display*
format has no effect on how values are interpreted by XL's parsing
engine (except that setting format to Text bypasses the parser
altogether).

So your input isn't being "rewritten", it was never stored the way you
wanted it.

XL stores times as fractional days, so 1 minute 30 seconds = 1.5/(24*60)
=.0.0010416666666667 No matter what the cell format, that value is
what's stored in the cell.

So yes, you have to enter 0:1:30 for 1 minute and 30 seconds.

OTOH, you can use VBA Event macros to change this behavior. Search the
archives:

http://groups.google.com/advanced_gr...ugroup=*excel*

for a myriad of ways.



In article ,
jazzsax505
wrote:

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!