why does changing date format in a cell have no effect?

English
#1

In Calc, (6.4.7.2, Linux Mint 20.1, default lang is UK Eng) I past data into a block of cells as unformatted text. One of the cells contains a date as, MM/DD/YYYY.

I select the cell, right click and choose “format cells”.

I ensure the cell is data type “Date” and then select format, DD/MM/YY.

The preview windows still shows the cell as, MM/DD/YYYY and after selection “OK”, the actual cell does not change format.

Even if I reset the cell and then change it back to “DATE” and “DD/MM/YY”, it has no effect.

image description
image description732×592 46.2 KB

The cell is not locked and protection is not turned on.

This issue has only existed for about 3 months, as I recall.

#2

I bet your date is not a calc date, but of data type Text (formatting a cell never changes the data type, when there are already data in the cell).

Set option View -> Value Highlighting and if your “date” does not turn to blue color then it is “Text”

See also FAQ

#3

You are correct, it doesn’t turn blue. Nor do the date cells contain the preceding apostrophe, indicating the data can be changed. I tried the ctrl “H” suggestions to no avail. The data is stuck. I even tried pasting it into cells previously formatted as “Date” but that didn’t work either. I guess I’ll just have to retype every cell.

#4

I did the ctrl “M” first, I just didn’t mention it. All the cells appeared to drop their formatting, as all the data shifted to the left, but the subsequent ctrl “H” had no effect.
Thanks for your help, though.

#5

Obviously the text string 06/28/2021 is not a valid date in the en-GB locale’s DD/MM/YYYY date order. Temporarily set the cells to an English-US MM/DD/YYYY date format before you attempt the Ctrl+H replacement of the FAQ, after which you can set the date format back to your default (en-GB) locale’s DD/MM/YYYY format.

Or follow the Find & Replace wrong date order section I just added now.

#6

I have don’t both, without success. I resorted to changing the entries manually. Now they respond as they should. Thank you.

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