# LibreOffice Calc converts date to clock face unicode character

Steps for replication:

Create new LibreOffice Calc document.

Format cell to Category Date with

Format code:

DD.MM.YYYY HH:MM:SS

Start entering date (don't copy and paste)

07/30/2019 14:12:22

After entering

07/30/2019 14:12

when you type another : it converts the text to

07/30/2019 14🕛

I would like to enter the date without this conversion happening.

Version: 6.2.4.2 Build ID: 1:6.2.4-0ubuntu0.16.04.1~lo1

edit retag close merge delete

I have followed the described steps and can't confirm the issue. I see exactly what I type: 07/30/2019 14:12:22. Question: why do you input 07/30/2019 (apparently MM/DD/YYYY) while the defined format is DD.MM.YYYY?

LO 6.2.5/Windows 7.

( 2019-07-30 15:01:10 +0200 )edit

On my machine the date is displayed as MM/DD/YYYY in the formula bar, even though the date format is different - I don't know why. EDIT: I added the version and build ID.

( 2019-07-30 16:52:36 +0200 )edit

Sort by » oldest newest most voted

This looks like a bug, and is likely a bug in some localization (the autocorrect entries are part of localization).

Back in 2016, when version 5.0 was released, where emoji support was introduced, we had an implementation error tdf#97191, that was treating the sequence colon-number-colon as a clock face emoji (like :1:🕐). But since that clashes with normal way of entering times, those entries were changed to strings like :1 h: since version 5.2.2. And the localizations followed (e.g., in Russian, it now accepts :1 ч:).

If you had that in 6.2.4.2, it could mean that some localization had that wrong in that version, and that localization was active for your input (a cell having that language set?) - I cannot test that myself at the moment. In that case, I'd expect a bug report on that, and also to be fixed immediately, in the next release (I don't believe that that kind of problem could go unnoticed for long) - please try version 6.2.5.

Or the incorrect entries could somehow appear in the settings (user entered them? a leftover from upgraded version 5.x?).

Please try to locate which autocorrect has the problem: your locale (see LibreOffice language settings to see which is active in the program - you said that's en_US)? your document language? a cell style language? Locating the wrong entries may help you to fix that by removing them, and also to file a bug if that turns out a localization issue (not a leftover frm an older installation).

Note that while you are entering anything into a cell, it is text yet - until you have finished entering, e.g. by pressing Enter in Calc; only at that point it tries to recognize the input (takes the entered string, and tries to interpret it as a number, or a date, or a percent, or currency, etc.) - so it's incorrect to apply the date/time internal representation (as a floating-point serial number in Calc) considerations to the process of input (before the input was finished), where anything is still text. You can see that the autocorrect happens while you are entering, not after you hit enter.

Also date acceptance patterns has nothing to do here, for the same reason.

more

A note: to check which cell language is set for a cell, you need to check the cell format's Font tab.

( 2019-07-31 00:02:47 +0200 )edit

The language set when clicking Format Cell and then Font is: English (USA). Under the AutoCorrect Options I have the English (USA) language setting. There I found the following entry

:12: 🕛

Which looks like the one which caused my problem.

( 2019-07-31 00:12:02 +0200 )edit

Hello

your problem seems to be based on a misconception of dates and times in Calc (and other spreadsheet applications). Date and times are stored as numbers in Calc (Date is the integer number of the days since 30.12.2899, while a time is the decimal fraction of a day). Thus entering a date in textual format always requires Calc to recognize the date and to convert it to a number, regardless of any format of the cell. The option which controls this functionality is: Tools -> Options -> Language Settings -> Languages -> Option: Date acceptance patterns.

A format does not control the input or conent of a cell but its representation. This is also true for date and time. You can recognize a real date (recognized by LibreOffice in the sense described above) by its alignment within a cell. If your input is left aligned, then LibreOffice did not recognize the input as a date. In contrary to that, a real date gets immediately right aligned (like all numbers) after your input.

more

There seem to be some hard coded date acceptance patterns which are active in addition to those specified at Tools -> Options -> Language Settings -> Languages -> Option: Date acceptance patterns (e.g. entering an ISO compliant pattern Y-M-D will be recognized by LibreOffice, even if it is not explicitly listed in Date acceptance patterns)

( 2019-07-30 15:51:18 +0200 )edit

It does get right aligned when I enter only 07/30/2019 14:12 However adding another : afterwards still converts to: 07/30/2019 14🕛

( 2019-07-30 16:50:02 +0200 )edit

From my answer, it should be clear, that any further help is only possible, if we know your Date acceptance patterns in your LibreOffice settings and the locale settings you have. An one more thing: The fact that you get the 🕛 Symbols is a clear indication that LibreOffice no longer recognizes your input as an date but a text an then starts to apply "AutoCorrect (Replace)" on text.

( 2019-07-30 17:04:56 +0200 )edit

Ok, the Date acceptance patterns are as follows: M/D/Y;M/D

The locale setting in calc is: Default - English (USA). My machine has the following locale:

LANG=en_US.UTF-8

LANGUAGE=

LC_CTYPE="en_US.UTF-8"

LC_NUMERIC=de_CH.UTF-8

LC_TIME=de_CH.UTF-8

LC_COLLATE="en_US.UTF-8"

LC_MONETARY=de_CH.UTF-8

LC_MESSAGES="en_US.UTF-8"

LC_PAPER=de_CH.UTF-8

LC_NAME=de_CH.UTF-8

LC_TELEPHONE=de_CH.UTF-8

LC_MEASUREMENT=de_CH.UTF-8

LC_IDENTIFICATION=de_CH.UTF-8

LC_ALL=

( 2019-07-30 18:01:28 +0200 )edit

## Stats

Seen: 117 times

Last updated: Jul 30 '19