Dates Values Toggle Between Date and Text When Changed

asked 2020-12-24 20:56:09 +0200

webmonk gravatar image

Let me keep this simple. Steps:

  1. New spreadsheet
  2. Highlight column A
  3. Press Ctrl-1
  4. Numbers tab Format code = DD MMM YY
  5. Press OK
  6. Go to cell A1
  7. Type 15 dec and press TAB
  8. You should now see 15 Dec 20 (or 21 if we are in 2021)
  9. Press the left arrow to go back to A1
  10. Type 15 dec and press TAB
  11. You will now see '15 dec (press F2 to see)
  12. The field converted to text verses staying with the previous date format and adding the year
  13. Go to step 9 and repeat several more times and you will see that it keeps cycling between the two formats

How do I fix this so it retains the date format every time?

Here is a video showing the steps as well.

edit retag flag offensive close merge delete


Input lines says: 12/15/2020 (yours), 15/12/2020 (mine).

Date acceptance patterns says: M/D/Y (yours), D/M/Y (mine).

Same language and user interface.

Try adapting your Date acceptance patterns to D/M/Y.

Tested with LibreOffice (x86); OS: Windows 6.1.

Add Answer is reserved for solutions. Click edit (below your question) to add more information.

LeroyG gravatar imageLeroyG ( 2020-12-24 22:21:58 +0200 )edit

That solution may work with date formatting in your part of the world, but that is not how dates are formatted here. In addition, I'm formatting the dates a certain way for a reason. Regardless of how the date is formatted, the result should not be toggling back and forth between two results when I'm entering the exact same thing. I'd like an actual solution to the issue since it seems to be some sort of bug.

webmonk gravatar imagewebmonk ( 2021-01-01 06:52:32 +0200 )edit

@webmonk, Your date acceptance pattern is: M/D/Y. You can type dec 15 as many times as you want, and you will have no problem. It has nothing to do with a "part of the world" but with your Locale setting:.

If you need to type 15 dec, change your date acceptance pattern to D/M/Y.

Maybe it is a bug (maybe not). Search at If nothing reported you can fill a bug report. Then share the bug number here (as tdf#nnnnnn where nnnnnn is the real number). Thanks.

LeroyG gravatar imageLeroyG ( 2021-01-01 12:38:01 +0200 )edit

My date acceptance patterns already include that. Here is what it shows: D M Y;M/D/Y;M/D;D M I didn't see the issue so I submitted a bug report. tdf#139517

webmonk gravatar imagewebmonk ( 2021-01-09 19:59:10 +0200 )edit

@webmonk, D/M/Y, no D M Y.

Thanks for sharing the bug report number.

LeroyG gravatar imageLeroyG ( 2021-01-10 15:40:49 +0200 )edit