number formatting

[Theresa]

Hi,

I need to enter times in a column (Excel 2003). For example, "00:40" -
meaning 40 secs. I've formatted the column as mm:ss, however when I enter
00:40, it converts the value to 40:00 in the cell, and reads 12:40 AM in the
status bar.

Any help would be greatly appreciated, thanks!
Theresa

 

[John C]

Even though you format the cells as mm:ss, when you actually enter the data,
you would need to enter it in hours:minutes:seconds, so, when entering data,
you would enter:
00:00:40, then it will display correctly.

 

[Theresa]

I guess Excel will always read it as time of day though, right? What I'm
really after is elapsed time, so I don't want it referenced from midnight or
whatever. Maybe I should just enter it as 40 (secs), so format the column as
general number, not time. Besides, entering 40 secs as 00:00:40 is a whole
lot of key strokes, which is really time consuming when entering a ton of
data! Oh well.

Thanks for your thoughts!
Theresa

 

[John C]

You could take a look at Chip Pearson's site. It may help you accomplish
what you want it too.
http://www.cpearson.com/excel/DateTimeEntry.htm

 

[David Biddulph]

0:0:40 will do, or 0:40.0

When Excel stores a time it makes no distinction between elapsed time
intervals and times of day. It is in the formatting of the display that you
can choose how to display it.

If you wish to work just in a number of seconds you can do so; if you then
want to turn the answer into a time, divide by 3600 and by 24 (or divide by
86400), and format in a time format to suit your preference.