topic Re: Strange Excel problem in Tech Help - Software/Hardware etc

Strange Excel problem

penneck — Thu, 14 Dec 2023 12:29:29 GMT

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

Re: Strange Excel problem

Gel — Thu, 14 Dec 2023 12:47:51 GMT

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

Re: Strange Excel problem

Champnet — Thu, 14 Dec 2023 12:48:21 GMT

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

Re: Strange Excel problem

MisterW — Thu, 14 Dec 2023 13:01:16 GMT

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.

Re: Strange Excel problem

penneck — Thu, 14 Dec 2023 13:43:21 GMT

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

Re: Strange Excel problem

RobPN — Thu, 14 Dec 2023 13:53:57 GMT

@penneck

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

Re: Strange Excel problem

MisterW — Thu, 14 Dec 2023 14:05:43 GMT

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

Re: Strange Excel problem

greygit1 — Thu, 14 Dec 2023 23:03:14 GMT

"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.

Re: Strange Excel problem

Mav — Sat, 16 Dec 2023 20:16:35 GMT

@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.

Re: Strange Excel problem

Mav — Sat, 16 Dec 2023 20:22:09 GMT

@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.

Re: Strange Excel problem

shutter — Sat, 16 Dec 2023 20:35:28 GMT

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.