Mend a Custom cell format mm:ss Bug
If I choose the cell custom format hh:mm:ss and enter 0:27:31 the cell shows 00:27:31 - As Expected.
If I choose the Cell custom format hh:mm and enter 16:54 the cell displays 16:54 - As Expected.
If I choose the cell custom format mm:ss and enter 27:31 the cell shows 31:00 - Completely UNEXPECTED
If I choose the cell custom format mm:ss and enter 27.31 the cell shows 26:24 - Even More unexpected.
This HAS been replicated by MS Chat who advised I post it here.
I CAN use the hh:mm:ss option and have 00: extra in every field though it IS ugly.
Please can someone advise when this format WILL work as expected given the behaviour of its neighbours and I can have the columns simply showing minutes and seconds?
Dervish McDroid commented
What does your answer have anything to do with excel Not Recognising minutes and seconds entered into a field formatted as mm:ss??
Or are you, as an employee of Microsoft Excel department, telling me Excel expects you to enter data into a field that is both unwanted and irrelevant to the field?
That the way Excel DESIGNED the program was for you to be forced to enter ZERO hours BEFORE entering the number of minutes and seconds you wish entered and calculable in the cell formatted for ONLY minutes and Seconds?
IF so are we meant to guess what other data we should enter into other cell formats that have truncatable formats, Why must I not enter the date, since the formula bar automatically completes a date when only a duration is entered?
Specifically, WHY would you have to enter Hours into a mm:ss formatted cell?
You seem to have Completely misread my post.
I made NO reference to entering 27:31 as hours and minutes.
But to paraphrase
"Buy your answer demonstrating your did not properly read MY question, . This error of understanding my question is YOUR shortcoming, not mine".
It is requiring you to enter the value in hours:minute:seconds form even though your output will only show the minutes and seconds.
For example, "0:27:31" and "13:27:31" will each display "27:31" because the hours portion is specified and Excel does not misinterpret the entry.
But since it is looking for hours first, regardless of format, it sees the "27" as hours and the "31" as minutes and displays the "31" only.
You don't have to use the "hh" part and have the ugly and distracting extra portion on display but you DO have to enter the hours.
The "27.31" oddness is due to it doing the arithmetic: 0.31 * 24 hours = 7.44 hours. "* 24 hours" because it is, in this case, running afoul of another convention it uses which is the integer portion is days, not whatever one has in mind when formatting, and the decimal portion is time. So it sees that as 27 days plus 0.31 more days so the arithmetic above. 0.31 days is 7.44 hours. 0.44 hours is 26 minutes and 24 seconds. Hence the "26:24" you get.
No great way around it. You can use a helper column and format it as text, enter the material as desired, then use a formula in the original column to select pieces from the entry and make a date out of them. But that, like most workarounds, sucks.
What you really want, as a Suggestion, is to suggest that one be able to set, along with the literal format for a cell, an option to allow Excel to interpret data entered in the output format as "belonging to" the format, do "mm:ss" and you enter "27:31" it realizes you mean 27 minutes and 31 seconds and treats it accordingly instead of thinking you mean 27 hours, etc. Enter something NOT matching the literal format set, like perhaps "13:27:31", and it would act normally (act like it does now).
But this behavior you show is intended. Excel feels it is YOUR shortcoming, not theirs.