Storing time as mm:ss.s

I am trying to store times in a table as mm:ss.s. I am recording race lap times so a typical entry may be 2:42.251. If I set the field as date time I can't format it to suit my needs, if I store it as text I can't use it for any calculations. Any ideas?

Jim O

Format cells with the following:

Please Login or Register  to view this content.

Or if you want to include hours, too;

Please Login or Register  to view this content.

No luck. I have the data value set to Date/Time and the format set as mm:ss.000 but it will not accept my data as valid. It wants to change it to a text format.

A question on the format for hours. If I used that format (assuming I can get it to work) but only had data such as 59:25.243 (not a full hour) would it display the time as 59:25.243 or would their be a conflict?

Jim O


mehmetcik, I can notdownload your file I am getting a corrupt file message.

Try entering =rand() in a cell and format as [h]:mm:ss.000

What do you see?

Originally Posted by JO505

It wont let me enter a formula in the field. I keep getting a msg that the data does not match the format, do I want to change the date to text.

Jim O

Maybe attach your workbook, so we can see what's happening.

Ah, sorry, I didn't spot this was in the Access forum. My bad.

As far as I know, Access only stores date/time fields to a precision of 1 second. You may have to format it a text field (with custom format) and find a way of converting from text to number to do any calculations, or store it as a number (total number of seconds, perhaps) and decode to the time format you want when reporting.

Originally Posted by OllyXLS

Ah, sorry, I didn't spot this was in the Access forum. My bad.

Ditto .