Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.

You will need to enter the leading zero for the hour, i.e.:

0:3:55

and then Excel will treat it as 3 minutes and 55 seconds and will
display it as 3:55 if you apply a custom format to the cell of [m]:ss.
The square brackets will cause it to display 1:05:55 (for example) as
65:55, i.e. 65 minutes and 55 seconds.

Hope this helps.

Pete

On Mar 25, 7:14*pm, GTifeld wrote:
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.

You need to enter it as 0:3:55. You can also check out this link if you are
not averse to using macros...

http://www.cpearson.com/excel/DateTimeEntry.htm
--
HTH...

Jim Thomlinson


"GTifeld" wrote:

Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.

For the Hour you enter =HOUR(A1) and for the Minutes you enter =MINUTE(A1),
and you must format both cells to "general". Let me know if this helps.

"GTifeld" wrote:

Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.

Hi Pete,

Thank you for your response, but perhaps I didn't phrase my question clearly
enough. I'm not looking to enter times as in the time of day. I'm looking to
enter time FRAMES or lengths.

For instance, if I enter 3:55, I want it to refer to the length of time as
having lasted for 3 minutes and 55 seconds. I don't want it to refer to 3:55
a.m.

Just for laughs, I tried entering the leading zero anyway and it still
tranlated it to the time of day. Before I posted, I tried formatting the cell
under "Custom", then chose "mm:ss", but it still turned it into a clock time
(12:03:55 a.m.).

I hope that makes more sense. If you or anyone can tell me how to do this,
I'd appreciate it. As I said before, I can't do it as straight text since
I'll need to do more calculations off those numbers later. Thanks.

--- Gary

"Pete_UK" wrote:

You will need to enter the leading zero for the hour, i.e.:

0:3:55

and then Excel will treat it as 3 minutes and 55 seconds and will
display it as 3:55 if you apply a custom format to the cell of [m]:ss.
The square brackets will cause it to display 1:05:55 (for example) as
65:55, i.e. 65 minutes and 55 seconds.

Hope this helps.

Pete

On Mar 25, 7:14 pm, GTifeld wrote:
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.

If you don't know how to enter time properly, you're going to have lots of
fun later trying to do time calculations. Perhaps you might want to read a
book on Excel such as one written by John Walkenbach http://j-walk.com/ss/.
He discusses Excel's dates and time formats among many other things. Easy
reading.

Good luck
Tyro

"GTifeld" wrote in message
...
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should
be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text, because
I'll
need to use it later in time calcs.

Thanks in advance.

0:3:55 or 3:55.0
--
David Biddulph

"GTifeld" wrote in message
...
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should
be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text, because
I'll
need to use it later in time calcs.

Thanks in advance.

That is how Excel deals with times, but if you want an alternative you
could enter the time as:

3:55.0

i.e. with a zero fraction of a second at the end. Don't worry about
the display in the formula bar, Excel will calculate times correctly
if you want to add or subtract them, but you must enter three elements
of the time - either a zero hour followed by a colon and then your
minutes:seconds, or as minutes:seconds followed by a point and a zero.
If you only enter two elements, i.e. 3:55, then Excel will assume this
means 3:55:00, i.e. 3 hours and 55 minutes.

Hope this helps.

Pete

On Mar 25, 8:06*pm, GTifeld wrote:
Hi Pete,

Thank you for your response, but perhaps I didn't phrase my question clearly
enough. I'm not looking to enter times as in the time of day. I'm looking to
enter time FRAMES or lengths.

For instance, if I enter 3:55, I want it to refer to the length of time as
having lasted for 3 minutes and 55 seconds. I don't want it to refer to 3:55
a.m.

Just for laughs, I tried entering the leading zero anyway and it still
tranlated it to the time of day. Before I posted, I tried formatting the cell
under "Custom", then chose "mm:ss", but it still turned it into a clock time
(12:03:55 a.m.).

I hope that makes more sense. If you or anyone can tell me how to do this,
I'd appreciate it. As I said before, I can't do it as straight text since
I'll need to do more calculations off those numbers later. Thanks.

--- Gary



"Pete_UK" wrote:
You will need to enter the leading zero for the hour, i.e.:

0:3:55

and then Excel will treat it as 3 minutes and 55 seconds and will
display it as 3:55 if you apply a custom format to the cell of [m]:ss.
The square brackets will cause it to display 1:05:55 (for example) as
65:55, i.e. 65 minutes and 55 seconds.

Hope this helps.

Pete

On Mar 25, 7:14 pm, GTifeld wrote:
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.- Hide quoted text -

- Show quoted text -

Hi Jim,

Thank you for your response, but as I replied to the answer before yours,
I'm thinking perhaps I didn't phrase my question clearly enough. I'm not
looking to enter times as in the time of day. I'm looking to enter time
FRAMES or lengths.

For instance, if I enter 3:55, I want it to refer to the length of time as
having lasted for 3 minutes and 55 seconds. I don't want it to refer to 3:55
a.m.

Just for laughs, I tried entering the leading zero anyway and it still
tranlated it to the time of day. Before I posted, I tried formatting the cell
under "Custom", then chose "mm:ss", but it still turned it into a clock time
(12:03:55 a.m.).

I hope that makes more sense. If you or anyone can tell me how to do this,
I'd appreciate it. As I said before, I can't do it as straight text since
I'll need to do more calculations off those numbers later. Thanks.

--- Gary


"Jim Thomlinson" wrote:

You need to enter it as 0:3:55. You can also check out this link if you are
not averse to using macros...

http://www.cpearson.com/excel/DateTimeEntry.htm
--
HTH...

Jim Thomlinson


"GTifeld" wrote:

Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should be
simple, but I can't seem to find the right format function. Can anyone tell
me what I'm missing? I don't want to enter it as straight text, because I'll
need to use it later in time calcs.

Thanks in advance.

Do you work in a bookshop, Tyro? You're always trying to get posters
to buy one !! <bg

Pete

On Mar 25, 8:09*pm, "Tyro" wrote:
If you don't know how to enter time properly, you're going to have lots of
fun later trying to do time calculations. Perhaps you might want to read a
book on Excel such as one written by John Walkenbachhttp://j-walk.com/ss/.
He discusses Excel's dates and time formats among many other things. Easy
reading.

Good luck
Tyro

"GTifeld" wrote in message

...



Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should
be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text, because
I'll
need to use it later in time calcs.

Thanks in advance.- Hide quoted text -

- Show quoted text -

Excel maintains time as fractions of 24 hours. 12 midnight is 0.0 1 hour is
1/24, 1 minute is 1(24*60), 1 second is 1(24*60*60)
So 3 minutes and 55 seconds is 1/(24*60) * 3 + 1(24*60*60) * 55, i.e.
0.00271990740740741.
If you display that as time formatted as mm:ss you will see: 03:55 If you
format that as h:mm:ss AM/PM you will see 12:03:55 AM
The formatting of the number is for human consumption. The time is still the
same. Your 3 minutes and 55 seconds is still 0.00271990740740741
I suggest you read some information on how Excel stores time.

Tyro



"GTifeld" wrote in message
...
Hi Pete,

Thank you for your response, but perhaps I didn't phrase my question
clearly
enough. I'm not looking to enter times as in the time of day. I'm looking
to
enter time FRAMES or lengths.

For instance, if I enter 3:55, I want it to refer to the length of time as
having lasted for 3 minutes and 55 seconds. I don't want it to refer to
3:55
a.m.

Just for laughs, I tried entering the leading zero anyway and it still
tranlated it to the time of day. Before I posted, I tried formatting the
cell
under "Custom", then chose "mm:ss", but it still turned it into a clock
time
(12:03:55 a.m.).

I hope that makes more sense. If you or anyone can tell me how to do this,
I'd appreciate it. As I said before, I can't do it as straight text since
I'll need to do more calculations off those numbers later. Thanks.

--- Gary

"Pete_UK" wrote:

You will need to enter the leading zero for the hour, i.e.:

0:3:55

and then Excel will treat it as 3 minutes and 55 seconds and will
display it as 3:55 if you apply a custom format to the cell of [m]:ss.
The square brackets will cause it to display 1:05:55 (for example) as
65:55, i.e. 65 minutes and 55 seconds.

Hope this helps.

Pete

On Mar 25, 7:14 pm, GTifeld wrote:
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It
should be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text, because
I'll
need to use it later in time calcs.

Thanks in advance.

I think if a poster read one, they might actually begin to learn Excel. But
as usual, most people won't RTFM.

Tyro

"Pete_UK" wrote in message
...
Do you work in a bookshop, Tyro? You're always trying to get posters
to buy one !! <bg

Pete

On Mar 25, 8:09 pm, "Tyro" wrote:
If you don't know how to enter time properly, you're going to have lots of
fun later trying to do time calculations. Perhaps you might want to read a
book on Excel such as one written by John Walkenbachhttp://j-walk.com/ss/.
He discusses Excel's dates and time formats among many other things. Easy
reading.

Good luck
Tyro

"GTifeld" wrote in message

...



Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It should
be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text, because
I'll
need to use it later in time calcs.

Thanks in advance.- Hide quoted text -

- Show quoted text -

Correction 1/(24*60) * 3 + 1(24*60*60) * 55 should be 1/(24*60) * 3 +
1/(24*60*60) * 55,

Tyro

"Tyro" wrote in message
...
Excel maintains time as fractions of 24 hours. 12 midnight is 0.0 1 hour
is 1/24, 1 minute is 1(24*60), 1 second is 1(24*60*60)
So 3 minutes and 55 seconds is 1/(24*60) * 3 + 1(24*60*60) * 55, i.e.
0.00271990740740741.
If you display that as time formatted as mm:ss you will see: 03:55 If you
format that as h:mm:ss AM/PM you will see 12:03:55 AM
The formatting of the number is for human consumption. The time is still
the same. Your 3 minutes and 55 seconds is still 0.00271990740740741
I suggest you read some information on how Excel stores time.

Tyro



"GTifeld" wrote in message
...
Hi Pete,

Thank you for your response, but perhaps I didn't phrase my question
clearly
enough. I'm not looking to enter times as in the time of day. I'm looking
to
enter time FRAMES or lengths.

For instance, if I enter 3:55, I want it to refer to the length of time
as
having lasted for 3 minutes and 55 seconds. I don't want it to refer to
3:55
a.m.

Just for laughs, I tried entering the leading zero anyway and it still
tranlated it to the time of day. Before I posted, I tried formatting the
cell
under "Custom", then chose "mm:ss", but it still turned it into a clock
time
(12:03:55 a.m.).

I hope that makes more sense. If you or anyone can tell me how to do
this,
I'd appreciate it. As I said before, I can't do it as straight text since
I'll need to do more calculations off those numbers later. Thanks.

--- Gary

"Pete_UK" wrote:

You will need to enter the leading zero for the hour, i.e.:

0:3:55

and then Excel will treat it as 3 minutes and 55 seconds and will
display it as 3:55 if you apply a custom format to the cell of [m]:ss.
The square brackets will cause it to display 1:05:55 (for example) as
65:55, i.e. 65 minutes and 55 seconds.

Hope this helps.

Pete

On Mar 25, 7:14 pm, GTifeld wrote:
Hi,

I'm trying to enter numbers as running times. For example, when I
enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It
should be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text,
because I'll
need to use it later in time calcs.

Thanks in advance.

Excel doesn't distinguish between an elapsed time and a time of day. It's
merely a question of how you format the data. If you put in 3:55.0 or
0:3:55, then:
if you format as [h]:mm:ss it will show as 0:03:55
if you format as [m]:ss it will show as 3:55
if you format as hh:mm:ss it will show as 00:03:55
if you format as hh:mm:ss AM/PM it will show as 12:03:55 AM
if you format as dd/mm/yyyy hh:mm:ss it will show as 00/01/1900 00:03:55
[The date shown is because day 1 for Excel is 1/1/1900, so day 0 is
00/01/1900, etc.]

In any of these cases, the number actually stored is 0.002719907, as times
are stored in units of 24 hours so if you format as General, 0.002719907 is
what you will see.
--
David Biddulph

"GTifeld" wrote in message
...
Hi Pete,

Thank you for your response, but perhaps I didn't phrase my question
clearly
enough. I'm not looking to enter times as in the time of day. I'm looking
to
enter time FRAMES or lengths.

For instance, if I enter 3:55, I want it to refer to the length of time as
having lasted for 3 minutes and 55 seconds. I don't want it to refer to
3:55
a.m.

Just for laughs, I tried entering the leading zero anyway and it still
tranlated it to the time of day. Before I posted, I tried formatting the
cell
under "Custom", then chose "mm:ss", but it still turned it into a clock
time
(12:03:55 a.m.).

I hope that makes more sense. If you or anyone can tell me how to do this,
I'd appreciate it. As I said before, I can't do it as straight text since
I'll need to do more calculations off those numbers later. Thanks.

--- Gary

"Pete_UK" wrote:

You will need to enter the leading zero for the hour, i.e.:

0:3:55

and then Excel will treat it as 3 minutes and 55 seconds and will
display it as 3:55 if you apply a custom format to the cell of [m]:ss.
The square brackets will cause it to display 1:05:55 (for example) as
65:55, i.e. 65 minutes and 55 seconds.

Hope this helps.

Pete

On Mar 25, 7:14 pm, GTifeld wrote:
Hi,

I'm trying to enter numbers as running times. For example, when I enter
3:55, I want it recognized as 3 min. & 55 secs.; Not 3:55 a.m. It
should be
simple, but I can't seem to find the right format function. Can anyone
tell
me what I'm missing? I don't want to enter it as straight text, because
I'll
need to use it later in time calcs.

Thanks in advance.