mm:ss keeps changing to a date format

[DavARei]

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....

 

[Gary''s Student]

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.

 

[Sheeloo]

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,...)

 

[Ron@Buy]

Try setting cell format to [hh]:mm

 

[DavARei]

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

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

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....

 

[DavARei]

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.

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,...)

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....

 

[DavARei]

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

Try setting cell format to [hh]:mm

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....

 

[David Biddulph]

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.