Malfunction of date formatting when editing in Calc

[jsheats]

Date formats are suddenly ( behaving inexplicably. If I edit a cell with a valid date (I know it’s valid because it can be used with a time function), it turns to text. It seems as if some setting has changed, but I cannot figure it out.
LO Calc, v. 7.4.7.2 (just installed), on Mac with 13.2.1
Thanks for any help.

[keme1]

Either your edit makes the input invalid as a date, or the cell has been set to interpret all input as verbatim text after the date was entered.

Check that the locale setting is what you expect

Date format is linked to national standards, known as “locale”. Most English language locales seem to use some mm.dd.yyyy layout. Many other locales will use dd.mm.yyyy layout. Separator character also varies.
See menu item Tools - Options, branch Language settings - Language. Does the locale (nationality) make sense?

Check cell formatting

• Select the problematic date cell.
• Select menu item Format - Cells …
• Select tab Number
• Check that …
  • in the Category pane, Number, Date or Time is selected (not Text)
  • the Language field shows correct locale (cf locale explanation above)

[jsheats]

“Format cells” options do not affect the behavior (neither Category nor Format nor Language). There is some kind of memory going on which I cannot understand the logic of: right now I can enter dates in the format “Jan 20 2023” and that displays properly and its format can be changed. But editing that turns it to text. I’m about to go nuts; have to leave it for awhile. Thanks.
(I didn’t try to change the locale in Mac settings. But the Calc language doesn’t affect it. I used to be able to select whatever format I wanted: 1/1/2020; 1 Jan 2020, etc. I can do this now, but only if I’ve entered it in a way that it accepts as a date. That’s why I say it seems to have assigned a format to the cell that I can’t change and can’t see.)

[Zizi64]

@jsheats

There is some kind of memory going on which I cannot understand the logic of: right now I can enter dates in the format “Jan 20 2023” and that displays properly and its format can be changed. But editing that turns it to text.

Plese upload a small ,ods sample file here with your date and settings inside,

[jsheats]

Here is the file. The entries to the right of the regular columns are my attempts to diagnose it. I don’t know how to add “settings” other than what might be embedded, but I’ll do what I can if more is needed.
Untitled 2 copy.ods (26.9 KB)
Thanks again.

[Zizi64]

Your dates appeared with Roman month numbers in my localized (Hungarian) LibreOffice (7.4.6)

It is better to use the international standard ISO date format:
yyyy-mm-dd
It is gives you same result in any locale.

[jsheats]

Unfortunately, the choice of format doesn’t solve the problem: I can change the format of any valid date entry. But editing any of those cells changes the content into text (the number goes to the left in the cell, loses its format, and it cannot be used in a date-related formula).

[Wanderer]

@jsheats

used to be able to select whatever format I wanted: 1/1/2020; 1 Jan 2020, etc. I can do this now, but only if I’ve entered it in a way that it accepts as a date. That’s why I say it seems to have assigned a format to the cell that I can’t change and can’t see

I think you should check “date acceptance patterns”. The link below shows a thread wich has some pictures.
Also try safe-mode …

Can't Edit Date Acceptance Patterns

I’m trying to edit the Date acceptance patterns under the Language Settings > Languages section of Calc and it is misbehaving (for lack of a better term). As soon as I try to type into the field, it duplicates what’s there and the box turns red. Any additional characters I enter compounds the issue, creating a string that is invalid and useless. [LO-calc-setting-bug] In the attached image I select the field, type ‘;d’, and as soon as I type the second ‘d’ it goes haywire. Has anyone see thi…

[EarnestAl]

I can edit 20 Jan 2020 to 20 Jan 2021 without problem. If you restart in LibreOffice Safe Mode, Help > Restart in safe mode > Continue in Safe Mode and edit the sheet does it work properly?

There might be corruption in the user profile, LibreOffice user profile - The Document Foundation Wiki

[jsheats]

That (safe mode) does work. The simplest fix offered doesn’t help, so I guess I will have to delve into the “compare profiles”; ugh. But apparently this is the solution; thank you very much!

[Wanderer]

@jsheats

delve into the “compare profiles”; ugh

I’d suggest to rename your old profile-folder. So you get a new profile. Then salvage things you are missing (macros in my case, dictionaries, extensions) or settings (database connections, altered menu etc.)

[erAck]

@jsheats

right now I can enter dates in the format “Jan 20 2023” and that displays properly and its format can be changed. But editing that turns it to text

This likely is a mismatch of locale and date acceptance patterns.

1. What is the locale (not the UI language) you are working in in LibreOffice? I’d assume en-US if your short month name date displayed is “Jan 20 2023”.
2. What is the string presented when editing the date? From 1. I’d assume 01/20/2023
3. What are the date acceptance patterns? I’d assume not the en-US M/D/Y;M/D. If they are D/M/Y or something else instead then obviously the 20 can not be month number and the input is not accepted as date.

Locale and date acceptance patterns can be found in LibreOffice Preferences on Mac, Language Settings, Languages. To switch date acceptance patterns back to the locale’s default, empty the field once and hit Apply, which should restore the patterns.