Cannot enter milliseconds (mm:ss.00 format)

[AnnaH25]

I have a column of times (children's swimming times) that look like this:
0:31.41
0:35.78
0:38.92
0:39.22
I have formatted to "mm:ss.00;@", still I can not do any operations on them (adding, subtracting). They seem to be identified as text and they stay in the left end of the cell. If I take a blank cell, format as above and enter a single '0' it will appear as "00:00.00" in the right end of the cell. If I enter '0:0:10' into this cell, it will appear as "00:10.00", but if I try entering anything with a comma, like '0:0:10.10' it seems Excel cannot interpret it as a time format. It will be shifted to the left of the cell, and if I try anything like A1+A2 I will just get #value. If I enter '10.10' in a blank, time formatted cell it will be interpreted as October 10th 2014. When I try changing the formatting of one of the cells with the swimming data, for instance between "mm:ss.00" and "mm:ss.000" the box called 'Sample' should update to show more or less zeros, but it does not.

I'm stuck, please help out!

[adyteo]

convert them into numbers and then display the format you need.

[protonLeah]

Use: mm:ss.000
and use 00: for minutes vs 0:

[AnnaH25]

@adyteo: The entry that is "0:31.41" cannot be converted into a number. If I just enter 31.41 as the number and then change it into mm:ss:00 it will return 50:24.00. (This is as expected considering how the time formats in excel works).

@protonLeah: Changing the number of zeros at the front or the end also unfortunately does not make a difference. I can do any combination like
0:34.41 m:ss.00
00:34.410 mm:ss.000
This does not make a difference.

So thank you for your efforts, but I can still not use my milliseconds to do any kind of operations.

I just discovered that even if I enter
=TIME(0,0,42.59)
into a cell that is formatted 'mm:ss.000' it will return 00:42.000.

[Pepe Le Mokko]

Which regional settings are you using?

[AnnaH25]

Which regional settings are you using?

It's set to English (United States), home location China, decimal symbol '.'. I am Norwegian and have been using some European locale earlier but had to change it about a year ago as my American accounting SW couldn't handle comma as a decimal symbol.

It's pretty frustrating, from what I've read on-line other people seems to manage by just setting the formatting right.

[adyteo]

use this formula to convert the strings into numbers that then you can use for math:

Please Login or Register  to view this content.

where A1 contains your first string (0:31.41) and the formula is in B1

[protonLeah]

To attach a Workbook

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[joeu2004]

I have a column of times (children's swimming times) that look like this:
0:31.41
0:35.78
0:38.92
0:39.22
I have formatted to "mm:ss.00;@", still I can not do any operations on them (adding, subtracting). They seem to be identified as text

The format of the cell has nothing to do with how Excel interprets data entry.

Try entering 0:31.41 manually. It works just fine for me.

It does not matter whether you have 1, 2 or 3 digits after the decimal point. It does not matter whether minutes is zero or anything greater.

[EDIT] In fact, you can enter more than 3 digits after the decimal point. But Excel rounds the data entry to 3 decimal places.

If it is being interpreted as text, the problem is likely to be one or more regular spaces before 0:31.41, or non-breaking spaces (HTML nbsp) before or after 0:31.41. (Regular spaces after 0:31.41 is not a problem.)

What is the source of the data?

If you copy-and-paste from a webpage, it is very common to get non-breaking spaces. When that happens, first I paste into Notepad, then copy from Notepad and paste into Excel.

I just discovered that even if I enter
=TIME(0,0,42.59)
into a cell that is formatted 'mm:ss.000' it will return 00:42.000.

Excel TIME only accepts integer parameters. Non-integers are truncated, as you discovered.

To enter non-integer time, use the format =h/24 + m/1440 + s/86400, where h, m and s can be any decimal number.

[AnnaH25]

use this formula to convert the strings into numbers that then you can use for math:
where A1 contains your first string (0:31.41) and the formula is in B1

Thank you so much for your effort! If I have "0:31.41" formatted as text in A1 your formula returns 00:59:02.400. If I format as a number your function will return 31.041 (it's a matter of tweeking number of input digits vs the /1000 right, no worry).

After some more testing, if you enter =TIME(0,0,1) and format the output as a number, you can see how 1 second in excel has the value 0.0000115 (and a lot of more digits). I could modify your function to instead of multiplying with 60 for minutes, to multiply with whichever value excel uses for minutes and similarly for seconds to yield the following function:

Please Login or Register  to view this content.

Now I get my output formatted as time with minutes, seconds and milliseconds. It is pretty insane that all of this effort is required for formatting from a text to a time. To me it seems that all second information is only counted as integers, for instance TIME(h,m,s) will only return seconds as an integer. Sigh...

[AnnaH25]

To enter non-integer time, use the format =h/24 + m/1440 + s/86400, where h, m and s can be any decimal number.

Thank you so much! It seems our replies just crossed each other. I didn't know the time formatting was as easy as you describe, so I used the TIME function to accomplish the same thing. The function I landed on can probably be simplified.

BTW, there is no whitespace either in front or after the text in the cells. If I enter '0:31.41' manually into a cell that is formatted 'mm:ss.00' Excel cannot handle it, it will not be interpreted as a time. If I enter something without the comma it works. I suspect some deeper problem with my dot (./,) locale, because this seems to work for other people as far as I can read on-line.

[joeu2004]

BTW, there is no whitespace either in front or after the text in the cells. If I enter '0:31.41' manually into a cell that is formatted 'mm:ss.00' Excel cannot handle it, it will not be interpreted as a time. If I enter something without the comma it works. I suspect some deeper problem with my dot (./,) locale

Yes. If you need to handle both decimal separators, you will need to something like the following:

=IF(ISNUMBER(A1),A1,--SUBSTITUTE(A1,".",","))

That might be too simplistic. It assumes your input is always of the format m:ss.s. If you cannot count on that and you need help with alternatives, let us know all the forms the data input might take (e.g., m, m:ss, m:ss.s and ss.s?).

[EDIT] Oh, I just looked at your previous response (#10), and it looks like you have an alternative that might work well enough for you.

However, if it is just a configuration problem, you might look in two places:

1. In Excel, click on File, Options, Advanced, and page down to "Use system separators". Be sure it selected.

2. In Windows, go to the Regional and Language Options control panel, click on Regional Options, Customize, and be sure Decimal Separator is period (".").

[AnnaH25]

1. In Excel, click on File, Options, Advanced, and page down to "Use system separators". Be sure it selected.

2. In Windows, go to the Regional and Language Options control panel, click on Regional Options, Customize, and be sure Decimal Separator is period (".").

I only have "." in my file as decimal separators, "Use system separators" is checked and Decimal Separator is set to period. It really is awkward why I cannot enter the milliseconds directly if everybody else can.

Thank you for the effort you're putting into this!

[AnnaH25]

Here it is in case anybody feels like looking at it. It's not a big thing, it's just a nuisance that I couldn't get it to work. I have more results to add, I just wanted to get the old ones working first.
swimming.xlsx

[joeu2004]

However, if it is just a configuration problem, you might look in two places:

1. In Excel, click on File, Options, Advanced, and page down to "Use system separators". Be sure it selected.

2. In Windows, go to the Regional and Language Options control panel, click on Regional Options, Customize, and be sure Decimal Separator is period (".").

I only have "." in my file as decimal separators, "Use system separators" is checked and Decimal Separator is set to period. It really is awkward why I cannot enter the milliseconds directly if everybody else can.

You did not mention your configuration in the Regional and Language Options control panel. It might specify comma as the decimal separator.

Here it is [the file] in case anybody feels like looking at it.

I have no problem entering, for example, 0:32.97 into that file and Excel interpreting that as time in the form m:ss.00.

Moreover, =--C3 works (converts C3 to Excel time), suggesting that there are no extraneous characters that might alter Excel's interpretation.

So again, I suspect the problem is with your system configuration, specifically the specification of the decimal separator.

By the way, the following is a simpler formula for converting text in the form "m:ss.00" into Excel time without assuming what the system decimal separator is:

=LEFT(C3,FIND(":",C3)-1)/1440 + MID(C3,FIND(":",C3)+1,5)/86400

formatted as Custom mm:ss.00.

Aside.... On systems with comma as the decimal separator, I would be surprised if the Custom format is mm:ss.00 instead of mm:ss,00. Arguably, perhaps Excel is not so flexible. But if it is, I wonder if that points to an "inexplicable" inconsistency in the narrative.

[Pete_UK]

Hi Joe,

good to see you posting on this forum. Hope you are well.

(Sorry for interrupting).

Pete

[joeu2004]

Aha! My bad: I overlooked these details.

If I enter '10.10' in a blank, time formatted cell it will be interpreted as October 10th 2014.

It's set to English (United States), home location China, decimal symbol '.'.

But apparently, the date separator is also set to ".". That is the root cause of your difficulty.

If you change the date separator to slash ("/"; US default), 12:34.56 is interpreted as mm:ss.00.

Explanation....

When both the decimal separator and the date separator are period ("."), there is an ambiguity. How should 10.10 be interpreted: as 10 + 10/100, or as Oct 10? (Rhetorical.)

If we enter 10.10 into a cell formatted as General, Excel interprets it as 10 + 10/100.

But if we enter 10.10 into a cell formatted as Time, Excel interprets it as Oct 10.

I infer that when Excel expects "date" input (really date and/or time), period (".") is interpreted as a date separator.

When we enter 12:34.56, whether the cell format is General or Time, colon (":") tells Excel this is "date" input.

But in that context, the form x:y.z cannot be intepreted as a date. That is, "x:y" is not a month (or day, depending on the short-date form regional configuration).

So Excel interprets it as text.

If you do not want to change the date separator to slash ("/"), I think you must convert text to numeric time yourself.

So I hope you did not overlook my previous suggestion, to wit....

By the way, the following is a simpler formula for converting text in the form "m:ss.00" into Excel time without assuming what the system decimal separator is:

=LEFT(C3,FIND(":",C3)-1)/1440 + MID(C3,FIND(":",C3)+1,5)/86400

formatted as Custom mm:ss.00.

If you prefer to use TIME instead of "magical" numbers like 1440 and 86400, use:

=LEFT(C3,FIND(":",C3)-1)*TIME(0,1,0) + MID(C3,FIND(":",C3)+1,5)*TIME(0,0,1)

[AnnaH25]

You did not mention your configuration in the Regional and Language Options control panel. It might specify comma as the decimal separator.

Sorry, I wasn't quite clear, that's what I meant when I wrote "Decimal Separator is set to period". If I open the control panel->Clock, Language, and Region->Change date, time, or number formats->Additional Settings I come to the place where I can see 'Decimal Symbol' and it is set to '.'. About a year ago I had to change my locale to US English and separator from ',' to '.' because my US accounting SW couldn't handle the ',' separator. I suspect that somewhere, deep down in my system, this has caused a conflict. Apart from the time formats, everything else works with the '.'. I have some custom settings for displaying time and date as I'm used to the European way (13:00 and not 1 PM), but it really shouldn't affect this.

Thank you for tidying up the function. Neat and tidy is good.

[AnnaH25]

If you change the date separator to slash ("/"; US default), 12:34.56 is interpreted as mm:ss.00.

I had my date format as yyyy.mm.dd, and changing it to yy/mm/dd or yyyy-mm-dd both makes the milliseconds work. My other excel files with dates seem to cope just fine with that change. Thank you also for the thorough explanation, it completely makes sense.

I never expected or intended for my petty list of swimming competition results to render this amount of work and replies, but I am really grateful! Those kind of annoyances just bug me, I really love to have an answer!

[joeu2004]

Hi Joe, good to see you posting on this forum. Hope you are well.

@Pete, good to hear from you.

@Anna, glad to hear the date format change was satisfactory.