Formatting in Minutes, Seconds and Milliseconds only

[amanda_m_wilson]

I am trying to format a time in Minutes, seconds and Milliseconds only.

If I utilise the Custome Format and place mm:ss.00 in the format Excel alters the display it a AM/PM format in the formula bar.

I have listed an example below:
Entered into cell: 05:03.97
Display in Formula Bar: 12:05:04 AM

I am trying to use this format to calculate race times - where there is no need for an hour.

In addition to this, is there a way that we could enter the data into the cell without having to place the : or . between the figures.
For example:
Enter 050397
Excel translates to 05:03.97 automatically.

I have tried to apply the formatting to the number after entry but excel automatically converts this to 12am and adds a date.

Hoping you can help

[sglife]

You can use the following formula to simplify your typing work
Eg, you can type the time in your prefer format, like "050599" in cell A13
Remember to set the cell A13 to "TEXT" format before you key in anything.
Then in another cell, say B13, keyin the formula

Please Login or Register  to view this content.

[amanda_m_wilson]

the problem with this is that it does not take into account that the middle digits can not be greater than 60.

Any ideas anyone?

[martindwilson]

with a1 formatted text and b1 formatted mm:ss.00
try in b1
=VALUE(LEFT(A1,2)&":"&MID(A1,3,2)&"."&RIGHT(A1,2))
any 6 digit no entered in a1 resolves to time
the cells in b can be added.
even if you go wrong and put 006103 (61 seconds)
this will change to 01:01.03
as an extra precaution use data validation so text length is = 6

[amanda_m_wilson]

Thanx for your help guys. I was able to use the top formula and place some vba code around it to identify any errors.

[Cheeky Charlie]

Further to other questions already solved, use format [m]:ss.00

HTH

[abdullah_alamin]

Hi, I need help
I am using Data validation so that i can limit users to input time in any other format than mm:ss.00
is there any way to do so?
i tried applying custom formula =Text(C4,"mm:ss.00") but in this way when i enter correct format as well it gives error

[FDibbins]

abdullah_alamin welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.