Entering/Formatting Time in Cells

[GTifeld]

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.

 

[Pete_UK]

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

 

[Jim Thomlinson]

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

 

[art]

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]

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

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

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.

 

[Tyro]

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

 

[Pete_UK]

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

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

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.

- Show quoted text -

 

[GTifeld]

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

 

[Pete_UK]

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

Pete

 

[Tyro]

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

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

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

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.

 

[Tyro]

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

Tyro

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

Pete

 

[Tyro]

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

Tyro

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

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

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

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.