Custom format mm:ss

[Guest]

I have formatted a column of cells to collect elapsed times using the custom
format mm:ss. I plan to keep a running total of the elapsed times in another
column.

When I type in elapsed time data of 30 minutes and 15 seconds (30:15), it
converts it to 15:00, short for 1/1/1900 6:15:00 AM.

What am I doing wrong?

Thanks

 

[Peo Sjoblom]

You need to enter it as 00:30:15
Excel thinks that 30:15 is 30 hours and 15 minutes and that is one day, 6
hours and 15 minutes thus the date you got since Excel counts 1 as one day
and they started their dates on Jan 0 1900


--


Regards,


Peo Sjoblom

 

[Guest]

When I type in the leading 00, (00:30:15), it converts the elasped time data
to an actual time: 12:30:15 AM, and displays it as 0:30:15 in the cell.
That's closer to what I was trying for, but still not quite right.

The format doesn't seem to suggest someone would need to type in the leading
00 to use this format, nor does it imply its changing the data into an actual
time. Is this the correct format for what I'm trying to do?

Thanks again!

 

[Peo Sjoblom]

That is correct, as long as you have your regional setting in windows set to
AM/PM it will reflect in the formula bar but you can use a custom format in
the cell using mm:ss and if entered 0:30:15 it will display (in the cell) as
30:15. Format you displays things, 30:15 will is 30 hours and 15 minutes
If you change your windows regional setting sin the control panel to
HH:mm:ss (24 hour format) it will be displayed that way in the formula bar
as well meaning that a value entered as 00:30:15 will be displayed that way
in the formula bar so if that is bothering you might want to change the view
under tools>options>view to not show the formula bar. You can also (if you
don't need to use this in any calculations) format the cell(s) as text and
enter 30:15 which will be reflected in the formula bar as well


--


Regards,


Peo Sjoblom