Ask Your Question

LibreOffice Calc converts date to clock face unicode character

asked 2019-07-30 14:30:21 +0200

jimmypants gravatar image

updated 2020-08-07 20:08:12 +0200

Alex Kemp gravatar image

Steps for replication:

Create new LibreOffice Calc document.

Format cell to Category Date with

Format code:


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: Build ID: 1:6.2.4-0ubuntu0.16.04.1~lo1

edit retag flag offensive 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.

gabix gravatar imagegabix ( 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.

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

2 Answers

Sort by ยป oldest newest most voted

answered 2019-07-30 23:55:40 +0200

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

edit flag offensive delete link more


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

Mike Kaganski gravatar imageMike Kaganski ( 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.

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

answered 2019-07-30 15:05:33 +0200

Opaque gravatar image

updated 2019-07-30 15:21:45 +0200

EasyTrieve gravatar image


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.

edit flag offensive delete link 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)

Opaque gravatar imageOpaque ( 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๐Ÿ•›

jimmypants gravatar imagejimmypants ( 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.

Opaque gravatar imageOpaque ( 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:
















jimmypants gravatar imagejimmypants ( 2019-07-30 18:01:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-30 14:30:21 +0200

Seen: 117 times

Last updated: Jul 30 '19