I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

The formatting only affects the display of time. If you have formatted the
cell to mm:ss and want to enter 12 minutes, 5 seconds; you still must enter:

00:12:05

you still need to enter those zero hours.
--
Gary''s Student - gsnu200835


"DavARei" wrote:

I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

Excel treats all dates as number of days from 1/1/1900 so when you enter
36:22 it interprets it as one day, 12 hours and 12 minutes which is 1/1/1900
12:22 PM.

If you convert it to decimal you will get 1.52 which when multiplied by 24
would give you 16.50 (sixteen and HALF hours)...

Of course 36:22 as 'time' does not make sense, it does make sense as 36
hours and 22 seconds... unfortunately Excel does not support that (similary
for feet and inches, miles and yards,...)


"DavARei" wrote:

I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

Try setting cell format to [hh]:mm

"DavARei" wrote:

I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

Thanks for the reply.
This does make the display appear the way it should, but it does still
appear to read (in the bar above) as [12:12:05 am] as if 12 minutes and 5
seconds is started from midnight ...
This will have to suffice.

Thanks again

"Gary''s Student" wrote:

The formatting only affects the display of time. If you have formatted the
cell to mm:ss and want to enter 12 minutes, 5 seconds; you still must enter:

00:12:05

you still need to enter those zero hours.
--
Gary''s Student - gsnu200835


"DavARei" wrote:

I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

Thanks for your reply.
I wasn't looking for 36 hours and 22 minutes, I was looking for entering a
race time of 36 minutes and 22 seconds.
And when I use the advice above, it will display it right ... though it
still reads it as a time. 1 step away from thinking it's a date.
At any rate, thanks again for responding.

"Sheeloo" wrote:

Excel treats all dates as number of days from 1/1/1900 so when you enter
36:22 it interprets it as one day, 12 hours and 12 minutes which is 1/1/1900
12:22 PM.

If you convert it to decimal you will get 1.52 which when multiplied by 24
would give you 16.50 (sixteen and HALF hours)...

Of course 36:22 as 'time' does not make sense, it does make sense as 36
hours and 22 seconds... unfortunately Excel does not support that (similary
for feet and inches, miles and yards,...)


"DavARei" wrote:

I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

Thanks for reply,
It still has issues when I use that format.
I guess I simply have to enter race times that take minutes as 00 hours: ##
minutes: ## seconds.
Unless there is some other way ...

Thanks again

"Ron@Buy" wrote:

Try setting cell format to [hh]:mm

"DavARei" wrote:

I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....

You need to enter as 0:12:22 or as 12:22.0
Otherwise Excel will interpret your data as hh:mm, not mm:ss.

You may see something different in the formula bar, as that won't take
notice of your cell formatting, but for your cell formatting you may want
mm:ss, or [m]:ss if you prefer to see 83:45 rather than 1:23:45.
--
David Biddulph

"DavARei" wrote in message
...
I have what should be a simple issue.
I'm trying to enter race times as mm:ss and the entry keeps changing to a
date format.
so, if I enter 12:22, Excel changes it to [12:22:00 PM].
However, if I enter 36:22, Excel will change it to [1/1/1900 12:22 PM]

I've tried setting cell format to Custom and selecting the mm:ss option,
I've also tried the h:mm:ss and a few others.
Still no joy, Excel just doesn't want to cooporate.
PLEASE HELP....