[Speedskater]

Excel and short time periods.
For race results I use a custom cell format of  m:ss.00  for times like 1:01.33 and 0:59.61.
While they display and print correctly, but when I go back to edit the cell it's 12:01 AM.
Is there any other way of entering time?

[FullRangeMan]

Not much used format: 00h05m20s    23h59m30s

[Speedskater]

It seems that no matter what format I use, it reverts back to 12:00 AM to 12:04 AM range.

[FullRangeMan]

Its W10?
Maybe due your PC time/date format is in the US time.

[Doublej]

Excel Version?, 32 or 64 bit?,
OS version? 32 or 64 bit?
Same behavior on multiple files created from scratch?

[Speedskater]

Once again I failed to describe the problem.
If I enter a race time of 1:10.22 it will display that correctly.
If I enter a race time of 4:33.09 it will display that correctly.
But if later I go back to a cell to edit a time, I see 12:01AM and 12:04AM.
and I can't edit the fraction of a second.
 It maybe Excel 2007 but I can't find the about button. Although the XP version did the same thing.

[FullRangeMan]

Sure the default is US format time by Excel or W requirement, you may change it anytime.

[Doublej]

It does the same thing on Excel 2016.  I think its related to how Excel stores elapsed times as clock times. 4:33.09 is being stored as 12:04:33 AM or in your case just 12:04 AM which is why you see this when you go to edit it.

I believe you will notice similar issues with date entry. You can choose the display of a date but may see it differently in the edit box.


You can try different format types for the cells but I think it will still store x:xx.xx as a clock time no matter what format you choose.

The only way around it is to put a ' in front of the string. So you would enter the time as '4:33.09. Then when you go to edit it you will see '4:33.09 in the edit box.

In Excel 2016 it is smart enough to detect that it looks like a number you are trying to sort and thus sorts the field as a number.

[Peter J]

What I know about Excel you could put in a thimble and rattle, but I've gotten help for some out-of-my-league problems from the super-nerds here: ( and I mean that in the most appreciative way).

http://www.mrexcel.com/

[Speedskater]

My other option is to enter:

 '1:17.21  which will display as  1:17.21

But then it's just plain text.

[Doublej]

My other option is to enter:

 '1:17.21  which will display as  1:17.21

But then it's just plain text.

So? It's still can be searched and sorted.