cancel
Showing results for 
Search instead for 
Did you mean: 

Strange Excel problem

penneck
Aspiring Pro
Posts: 1,124
Thanks: 89
Registered: ‎03-08-2007

Strange Excel problem

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

10 REPLIES 10
Gel
Aspiring Champion
Posts: 2,365
Thanks: 296
Fixes: 29
Registered: ‎02-08-2007

Re: Strange Excel problem

Champnet
Aspiring Hero
Posts: 2,892
Thanks: 1,085
Fixes: 14
Registered: ‎25-07-2007

Re: Strange Excel problem

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

MisterW
Superuser
Superuser
Posts: 16,157
Thanks: 6,148
Fixes: 444
Registered: ‎30-07-2007

Re: Strange Excel problem

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.

Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.

penneck
Aspiring Pro
Posts: 1,124
Thanks: 89
Registered: ‎03-08-2007

Re: Strange Excel problem

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

RobPN
Seasoned Hero
Posts: 5,229
Thanks: 2,754
Fixes: 13
Registered: ‎17-05-2013

Re: Strange Excel problem

@penneck 

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

MisterW
Superuser
Superuser
Posts: 16,157
Thanks: 6,148
Fixes: 444
Registered: ‎30-07-2007

Re: Strange Excel problem

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

Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.

greygit1
Aspiring Pro
Posts: 411
Thanks: 56
Fixes: 1
Registered: ‎26-06-2023

Re: Strange Excel problem

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

Mav
Moderator
Moderator
Posts: 22,655
Thanks: 4,837
Fixes: 517
Registered: ‎06-04-2007

Re: Strange Excel problem

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

Forum Moderator and Customer
Courage is resistance to fear, mastery of fear, not absence of fear - Mark Twain
He who feared he would not succeed sat still

Mav
Moderator
Moderator
Posts: 22,655
Thanks: 4,837
Fixes: 517
Registered: ‎06-04-2007

Re: Strange Excel problem

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

Forum Moderator and Customer
Courage is resistance to fear, mastery of fear, not absence of fear - Mark Twain
He who feared he would not succeed sat still

shutter
Community Veteran
Posts: 22,295
Thanks: 3,811
Fixes: 66
Registered: ‎06-11-2007

Re: Strange Excel problem

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.