Editing Date/Time converts cell into text

English
#1

I am copying calls from a Google Sheets into a Calc ods file. Two columns have date and time in the format 03/10/2026 09:00:00 that should correspond to DD/MM/YYYY HH:MM:SS.

It is being rendered as DD/MM/YY HH:MM in the cells but the formula bar shows the DD/MM/YYYY HH:MM:SS format.

I needed to change the time of one of these entries from 09:00:00 to 13:00:00. As soon as I change the entry, it becomes a text cell (left justified and impossible to format in any other way.

I tried:

  1. Editing the content in the formula bar and in the cell.
  2. Changing the cell date format to the DD/MM/YYYY HH:MM:SS before editing.
  3. Changing the cell date format to the DD/MM/YY HH:MM before editing and entering the edited date/time in that format.

Here’s a redacted copy of the file:
Test.ods (10.9 KB)

Nothing seems to work to conserve the cell continue to be recognised as a date/time.

What am I doing wrong?

#3

Have you checked “date acceptance patterns”
They are responsible for your input and depend on locale.
.
The items you described only format output, if content of the cell is of type date (actually a number).

#4

Probably the reason for that change in cell format depends on “exactly” how you edit the content of the cell.

Taking your example, if you select the 09 and type in a 13 (and press [ENTER]), it should probably accept the new time. But, if you select 09:00:00 and type in 13:00 (or 13 alone), then possibly Calc will parse the new content as Text.

You should review the number format code ([CTRL]+[1] on the relevant cell/range), so you can either modify it according to your real usage / needs, or instead adapt the way you type in the data (i.e. date + time values) in those specific cells.

#5

I tried editing the cell content, by replacing 09 with 13. No other changes and the format changed even though the cell properties haven’t.

I found a workaround:

  1. I edited the date time value in the Google sheets.
  2. Copied rows 2 onwards and pasted them in Calc with ctrl + alt + shift + V and it recognised the dates properly.

This is not a solution, but a workaround. Furthermore, I had tried copying only the row I needed to edit alone and somehow this didn’t work, so I’m still confused.

#6

I am only describing the behavior I see / experience. What I described was based on what I tried (in order to replicate the behavior you described in your initial post).

Moreover, the cell format I see in your sample file uses 12hs AM/PM format, while you seem to be using 24hs format. And yet, I was able to replace the “09” with “13” and it was correctly accepted and displayed. Only when I intentionally changed the whole “09:00:00” to use some different format (e.g. no seconds, “13:00”), then it was parsed as Text.

Perhaps it varies with the version of Calc. Please post the version you are using (menu Help > About, use the icon in there to copy the info to the clipboard and paste it here).

#7

I’m using Version: 26.2.3.2 (X86_64)
Build ID: 620(Build:2)
CPU threads: 8; OS: Linux 7.0; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded

#8

I can replicate the problem. Apparently, if the month and the date values are both between 1 and 12, Calc overcomes the minor inconsistencies in the content (against the cell format and the Date acceptance patterns). But when at least one of those values (month or date) are higher than 12, Calc cannot parse the new input as date, so it takes the new content as Text. You can confirm it by using Value Highlighting.

Formatting the cells adequately, and setting the Date acceptance patterns according to your real usage (Tools > Options > Languages and Locales > General) should minimize this kind of situations.

1 Like
#9

I can edit the times in your cells any way I want, directly in cell (F2) or in the formula bar.
I get text when I use a time separator other than :
I get text when I enter a minute or second greater than 59.
I get text when I enter a minute without trailing : (6/10/26 9 instead of 6/10/26 9:).
Entering an hour greater than 23 increases the date portion according to the additional hours.

Version: 26.2.3.2 (X86_64)
Build ID: 70e089b17412e4cb7773e41413306b17a2328c34
CPU threads: 4; OS: Linux 6.12; UI render: default; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

The German locale makes no difference. I also tested with English(UK).
The attachment includes a cell style “Timestamp_en-UK”
Test_ask_135192.ods (10.9 KB)

#10

@entodoays, thank you for the interesting question.
In your file, the cells displayed as dates contain numbers (which is correct).
The following explanations apply to both Calc and Excel.
When you enter data into a cell or edit a cell, the program must determine what you’ve entered (we’re not considering formulas):

  1. Text that should be interpreted as a number
  2. Text that should be interpreted as a date
  3. Other cases (just text)

The type of the “old” cell value (when editing) is not taken into account.

The algorithm depends on the locale and additional information.
If you enter text as a date and time in the ISO format “YYYY-MM-DD HH:MM:SS”, it will be interpreted as a date and time regardless of the locale (by the way, this is the simplest solution in your example).
For further study see Date acceptance patterns

#11

Can you share a copy of the file with one cell showing the wrong formatting? Thanks.

#12

I added D/M/Y to the date acceptance patterns and now the problem seems to be gone.

1 Like
#13

This may lead to unexpected results with locale English (USA).

#14

I rarely use that locale. Mostly I stick with English (UK)

#15

Just in case of some US resident reading this.

1 Like
#2

hello
try with a macro with this example
macroDate.ods (15.8 KB)

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