cell formatting problems

[jazzsax505]

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!

[Ragdyer]

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" <jazzsax505.1ptaac_1117400705.5992@excelforum-nospam.com> wrote
in message news:jazzsax505.1ptaac_1117400705.5992@excelforum-nospam.com...
>
> 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
>

[JE McGimpsey]

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.1ptaac_1117400705.5992@excelforum-nospam.com>,
jazzsax505 <jazzsax505.1ptaac_1117400705.5992@excelforum-nospam.com>
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!