Strange Excel problem

[#1]

Anybody got an idea why, if I format a cell as mm:ss to put a time of minutes and seconds in it, I have to type in the actual time in the form 0:mm:ss? If I don't have that 0:, the time shows up as ss:00.

I'm using Excel fro Office 2007. Its old but you would have thought this problem would have shown up, and been fixed by the time Office 2007 was published

Thanks for any help

[#2]

This is Excel 2021, but have you selected this when formatting?

[#3]

Same problem in the latest version, expected input hh:mm:ss

12:05 dsplays as 05:00,  00:12:04 shows as 12:04

Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20028) 64-bit

[#4]

if I format a cell as mm:ss to put a time of minutes and seconds in it,

In reality the cell is formatted as 'time' , the mm:ss is the format you'd like it  displayed as

and

Time is universally entered in Hours:Minutes:Seconds format, so you can't change this entry format. You can obviously change the display format, but unfortunately you'll need to abide by the entry format.

[#5]

I never thought of it like that. It explains a lot. At least I now know I've not missed some setup that would have made it do what I wanted, rather than me having to do what I have been doing all along. I can't help feeling MS has missed a trick.

Anyway, thankyou to both of you

[#6]

@penneck 

It might be worth Googling to see if there are any workarounds using Macros.

[#7]

https://superuser.com/questions/1550343/enter-time-duration-in-excel-in-mm-ss-format-without-hours-a...

[#8]

"I can't help feeling MS has missed a trick."

Nah. They're selling training courses to explain why it is that way. They haven't missed a trick - at all.

I'm a YYYYMMDDhhmmdd. guy. That format rather helps with sorting stuff into correct chronological order. Numerically and textually.

[#9]

@greygit1 

I agree.

When saving documents like statements I always save a name in the style of YYY-MM-DD_Text as it's easier to keep in order rather than use the file created/modified dates as they could be out of chronological sequence.

[#10]

@penneck 

I find this series of videos by Leila Gharani really helpful when requiring some help in using excel. While she usually uses the latest version (whatever that may be at the time) much relates to earlier versions.

[#11]

I found this page a few years ago....   https://www.techonthenet.com/excel/index.php

Plenty of other stuff in the left hand menu..... so all in one place.