Entering/Formatting Time in Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GTifeld - 25 Mar 2008 20:14 GMT

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.

Reply to this Message

Pete_UK - 25 Mar 2008 20:22 GMT

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,
>
[quoted text clipped - 5 lines]
>
> Thanks in advance.

Reply to this Message

GTifeld - 25 Mar 2008 21:06 GMT

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.:
>
[quoted text clipped - 18 lines]
> >
> > Thanks in advance.

Reply to this Message

Pete_UK - 25 Mar 2008 21:14 GMT

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,
>
[quoted text clipped - 41 lines]
>
> - Show quoted text -

Reply to this Message

Tyro - 25 Mar 2008 21:25 GMT

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,
>
[quoted text clipped - 47 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Tyro - 25 Mar 2008 21:30 GMT

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)
[quoted text clipped - 62 lines]
>>> >
>>> > Thanks in advance.

Reply to this Message

David Biddulph - 25 Mar 2008 21:32 GMT

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.

Signature

David Biddulph

> Hi Pete,
>
[quoted text clipped - 47 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Jim Thomlinson - 25 Mar 2008 20:29 GMT

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

Signature

HTH...

Jim Thomlinson

> Hi,
>
[quoted text clipped - 5 lines]
>
> Thanks in advance.

Reply to this Message

GTifeld - 25 Mar 2008 21:15 GMT

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

> You need to enter it as 0:3:55. You can also check out this link if you are
> not averse to using macros...
[quoted text clipped - 10 lines]
> >
> > Thanks in advance.

Reply to this Message

art - 25 Mar 2008 20:33 GMT

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.

> Hi,
>
[quoted text clipped - 5 lines]
>
> Thanks in advance.

Reply to this Message

Tyro - 25 Mar 2008 21:09 GMT

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

> Hi,
>
[quoted text clipped - 8 lines]
>
> Thanks in advance.

Reply to this Message

Pete_UK - 25 Mar 2008 21:16 GMT

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

Pete

> 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
[quoted text clipped - 19 lines]
>
> - Show quoted text -

Reply to this Message

Tyro - 25 Mar 2008 21:28 GMT

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

On Mar 25, 8:09 pm, "Tyro" <T...@hotmail.com> 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
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Reply to this Message

David Biddulph - 25 Mar 2008 21:14 GMT

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

> Hi,
>
[quoted text clipped - 8 lines]
>
> Thanks in advance.

Reply to this Message