Calc: Entering minutes/seconds without autocorrect mangling

English
, , , , ,
#1

I am trying to enter times into Calc. I would prefer to enter m:ss, like 9:43 for nine minutes and 43 seconds, but that is automatically detected as h:mm (specifically as 9:43am). I often like to type time-of-day entries this way, so I don’t want to alter that behavior. A few years back I learned the work-around: type that as h:m:ss, with h=0. So that becomes 0:9:43. At some point I also learned I needed to precede single minutes by a zero, so that would be 0:09:43. A little annoying, but I can train my fingers (and have).

Lately I’ve found my work-around more and more annoying. I can understand entering 9 as 09, but 10 as 010? The problem has to do with entries :1:, :2:, …, :9:, :10:, :11:, and :12: in the auto-replace table. These get replaced with some character that does not exist in my default font. So I either type 10:43 as 0:010:43, or I type 0:10:43.

I have a better idea: disable AutoCorrect on the current Calc document. How do I do this? The top web hit (duckduckgo) for “libreoffice disable autocorrect”, entitled “Turning Off AutoCorrect - LibreOffice Help”, only applies to Writer, and does not contain any link to a document explaining how to do this in Calc. The next web hit is “AutoCorrect - LibreOffice Help”, which has a promising link at the top of the page: “When in Calc: Deactivating Automatic Changes.” Clicking on that takes me to Deactivating Automatic Changes - LibreOffice Help, which discusses disabling AutoInput, but not AutoCorrect. Those are two different things.

Is there a way to disable AutoCorrect on the current Calc document?

Edit: I enter these as actual times, not just representations of times, because I calculate durations from their differences. Ultimately this is used as a tool for assigning point values to exam questions, with longer questions typically getting more points.

This is definitely an AutoCorrect issue. See Tools > AutoCorrect Options…, then scroll down the list under the Replace tab and find entries like :0:, etc.

I am using LibreOffice v5.1.6.2 on Ubuntu Mate.

#2

I don’t understand your remarks concerning the zero prefixed to the minutes part. It never was necessary.
The other point: Nothing you are talking of is actually related to AutoCorrect as far as I can see.
What actually happens is that an entry is “recognised” as a kind of “special number”, a TOD in this case, and then displayed in the respective default format. 0:9:43 should be recognized as 00:09:43 TOD (and probably displayed using the outdated AM/PM notation.

#3

@dcmertens: How you manipulate data you’re describing. You calculate differences, adding that up … ? Knowing what you plan to do with those intervals might help to figure out a workaround.

#4

@Lupp, I’m not sure what your first two sentences mean. See edit about why this is an AutoCorrect issue.

#5

@dcmertens: Sorry. I still don’t know your UI language, locale setting, document language…
I even changed to the UI language ‘English (USA)’ which is one of the worst to check for your claim. Nonetheless I cannot find a :0: replacement. Did you define one yourself?
BTW: The replacement entries with something enclosed by a pair of colons were introduced as an additional way to enter special characters (emojies i.s.). They should not conflict with the recognition of special numbers.

#6

Quoting @dcmertens: “I enter these as actual times, not just representations of times,”
Software doesn’t know “actual things” but always just representations. If the representations are ued regarding the standards, results should be as expected. To successfully calculate with times (durations) it is indispensable to know the differences concerning recognition, formatted display, and treatment as numbers on the one hand and the unit used on the other hand.

#7

@dcmertens I remember some issues with autocorrection in some earlier versions. @Lupp answered similar question already: I can not type “01:10:”. You use outdated version of LO, try update first.

#24

Here are two viable solutions to the actual problem based on comments from @erAck and @Lupp.

The underlying issue stems from a feature: LibreOffice makes it possible to insert unicode characters with a set of shorthand key sequences. For example, :beta: is replaced by the unicode representation of the lower-case Greek beta character. The problem is that some old versions of LibreOffice had replacements for :1: through :12: to the unicode representations for clock faces representing those times. Particularly annoyingly, in my font those clock faces do not have renderings, and so were just turned into empty squares. Presumably, :1.30: through :12.30: get replaced with the half-hour representations of those times, too.

To officially answer my question, one turns off autoreplace by doing the following. Through the menu system, select Tools > AutoCorrect Options… Click on the Options tab and uncheck the “Use replacement table”.

Another (arguably better solution) is to remove the offending clock-face unicode autoreplace magic. To do this, go to Tools > AutoCorrect Options… Under the Replace tab (the default tab for that dialog box), look for all entries that have the form of a colon-number-colon and either delete them or replace them with the current LibreOffice shorthand of :1 h:, :2 h:, etc. This effect is global: it will be remembered across all LibreOffice applications and will effect data entry in all documents, old and new.

#25

Bro, I told you this might be a problem in my second comment on your original question and I provided a link to similar topic so you can find out more. I also told you that you should update your LibreOffice version instead turning off autocorrection. For what I remember, that was fixed in later versions so go with 5.4.6 instead 5.1. That way February…

#8

Select rows or columns you need to enter 9:15 and 9:10. Then do right click and Format cell. Choose Time, enter M:SS in Format code field.

Enter 0:9:10 (result: 9:10) in first cell, then 0:9:15 in second cell (result 9:15). When you do =secondCell - firstCell, formula will return 0:05. Guess that’s what you’re expecting to get.

Although I don’t know why would you not want to enter hours as 0:09:15, because 0:9:15 is always internally saved as 12:09:15, you can’t do time without hours. You could save those values in separate cells as whole numbers (first column you enter 9, and 15 in second) and try to figure out by TIME formula or using math other ways and concatenate result. To many things can go wrong by trying to trick the office suite, user should not try to trick office suite.

You’re always better off with valid formats, what cost you to enter actual hours in format HH:MM:SS as everything would work like a charm. I know that’s not what you want exactly but does it work? If looks good and it should calculate well, if everything is done right. What’s the reason for avoiding hours since you enter data manually and you need to enter it like 0:9:15 anyway. If hours are always 0, intervals should be calculated correctly and the display of that in spreadsheet could be manipulated with Format code filed. It’s not clean way of doing this, but hey…

You can display only minutes and seconds with formating cell as described above but you can’t avoid entering hours if you want Calc to treat your entry as time.

[update]

Since OP updated question with information what he actually does with entries, I attach a spreadsheet that should help. It’s not elegant as what one would expect but with some extra columns (which you can hide and never see) or some extra sheets (so you link values from different sheet within a spreadsheet) you could easily calculate difference in duration.

I was going only for minutes, but this could be expanded further so hours are displayed also. Calc doesn’t need to know it’s doing time to actually do time: enter integers in two columns - minutes in left, seconds in right and do the rest indirectly. Computers work with representation, represent it in a way that works for you. If you need to compare something, use values represented in seconds.

I hope it helps…

#9

The question is about entering and formatting durations.
Quoting @Kruno: “enter M:SS in Format code field”
To allow for durations >= 60 min the code must be [MM]:SS (or probably [M]:SS).

Quoting @Kruno: “…because 0:9:15 is always internally saved as 12:09:15…” ???
What’s the idea? 0:9:15 if displayed based on a format code like H:M:S or a bit more reasonable, is internally represented by the dyadic equivalent of 0.00642361111111111 (or 37/5760 to be more precise).

#10

The question is about entering and formatting durations.
Quoting @Kruno: “enter M:SS in Format code field”
To allow for durations >= 60 min the code must be [MM]:SS (or probably [M]:SS).

Quoting @Kruno: “…because 0:9:15 is always internally saved as 12:09:15…” ???
What’s the idea? 0:9:15 if displayed based on a format code like H:M:S or a bit more reasonable, is internally represented by the dyadic equivalent of 0.00642361111111111 (or 37/5760 to be more precise).

#11

@Lupp - is it possible to enter ‘0:9:15’ in a cell so that formula bar is not showing ‘12:09:15 AM’? Is it possible to store only minutes and seconds without hours? What I would do is use two columns and enter minutes in one and seconds in another and then try to do whatever needs to be done. Quoting me shows my terminology being wrong, but can you help OP? I’m interested in this too.

#12

@Kruno: It’s a mess with formats, default formats, acceptance patterns, and everything related to the acute and progressive localitis we suffer from. Your 12:09:15 AM (suggesting 12=0) may be unavoidable if a UI languiage is set that uses the outdated AM/PM by default. However, that’s a string produced on the fly for display in the formula bar. The RAM rpresentation (internal) is an IEEE double floating point number. The persistent representation (file) may be decimal …

#13

OK, but that means that interval can’t be saved as 9:15 without hours regardless of how it is displayed. It may be IEEE double or outdated 12:09:15 AM notation but information about hours exists nonetheless? I don’t care about how is represented but I do care is storing time interval without hours and seams that 9:15 can’t be used for that. What’s left is time interval saved as integer representing seconds which makes 9:15 an int 555. Doesn’t my answer (kinda) work?

#14

Unfortunately this is incorrect. If I set the format to M:SS, then key in 9:55, Calc will display the result as 55:00. This means that keyed-in data of #:## is interpreted as H:MM, and changing the cell format does not appear to alter that interpretation.

#15

Any value formatted using the sexagesimal subdivision (1 h/60 = 1 min =60 s) is treated as “just a number” (IEEE double, 64 bit).
Aside of the capability to read a format which is as strange as something older than 4000 years can be we need to know the unit actually used. Spreadsheets inevitably use the day (more precisely 86400 s) as the unit of time though this is inconsistent and the day only is a calendaric term basically and undue to act as a unit. Spreadsheets are dumb insofar.

#16

@dcmertens: Formats and recognition patterns are different things.
The recognition of time values is one of the few cases where Calc goes the reasonable way and applies the standards explicitly specified by ISO 8601: If any part of a date or a time (TOD primarily) is omitted it must be less significant than the given parts. You may omit the day, but not the year of a date. You may omit the seconds but not the hours of a TOD.
BTW: Use [M]:SS to avoid bad surprises.

#17

@dcmertens setting format to M:SS doesn’t mean you can enter value as 9:15, you still need enter 0:9:15 and it will only shown as 9:15.

#18

(Once again:) The format code M:SS will not display durations of 60 min or more. It will suppress the hours-part despite the fact that it’s more significant. Use [M]:SS to see durations expressed in minutes and seconds with full hours accounting for 60 min each.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.