number formatting for time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

G Henry - 20 Mar 2006 18:44 GMT

I need to format a column to show only minutes and seconds in the 31:12
format. It must force the data to conform so the interns will not screw up
the table. I have tried all the options with none working. The format [h]:mm
works but the formula bar shows
1/1/1900 6:24:00 AM for the time 30:24
Please help.

Reply to this Message

Tom Ogilvy - 20 Mar 2006 18:50 GMT

the format should be [mm]:ss

if you entere 30:24, that is 30 hours 24 minutes. to enter 30 minutes and
24 seconds you must enter

0:30:34 or 00:30:24

Look at Data=>Validation if you want to restrict entry. the is a Time
option with parameters you can specify.

Signature

Regards,
Tom Ogilvy

> I need to format a column to show only minutes and seconds in the 31:12
> format. It must force the data to conform so the interns will not screw up
> the table. I have tried all the options with none working. The format [h]:mm
> works but the formula bar shows
> 1/1/1900 6:24:00 AM for the time 30:24
> Please help.

Reply to this Message

G Henry - 20 Mar 2006 18:57 GMT

Thanks, But we never need hours, just minutes and seconds. We are a radio
staion and this is FCC mandated informaiton so 55 minutes is the longest. Any
way to type in 30:24 and have it show 30 minutes and 24 seconds?

> the format should be [mm]:ss
>
[quoted text clipped - 12 lines]
> > 1/1/1900 6:24:00 AM for the time 30:24
> > Please help.

Reply to this Message

Mark Lincoln - 20 Mar 2006 19:21 GMT

If you don't need to add the times, you can use this custom number
format:

00":"00

and have your interns type in the information using just the digits.

Examples:

Typing 123 results in 01:23
Typing 1008 results in 10:08
Typing 3427 results in 34:27

This makes for quick entry as one needn't type the colon every time.

You can still use this and add the times, but you have to work at it
some. :)

Reply to this Message

Tom Ogilvy - 20 Mar 2006 19:27 GMT

you can enter 30:24 and it will be stored as hours, but you can mentally
treat it as minutes. Don't look at the formula bar it if bothers you. If
you do addition, you will be adding hours, but formatting the cell as [hh]:mm
will still "look" like total minutes.

Signature

Regards,
Tom Ogilvy

> Thanks, But we never need hours, just minutes and seconds. We are a radio
> staion and this is FCC mandated informaiton so 55 minutes is the longest. Any
[quoted text clipped - 16 lines]
> > > 1/1/1900 6:24:00 AM for the time 30:24
> > > Please help.

Reply to this Message

G Henry - 20 Mar 2006 21:05 GMT

Thanks, I appreciate all your help.

> you can enter 30:24 and it will be stored as hours, but you can mentally
> treat it as minutes. Don't look at the formula bar it if bothers you. If
[quoted text clipped - 21 lines]
> > > > 1/1/1900 6:24:00 AM for the time 30:24
> > > > Please help.

Reply to this Message