Formating Cell as User-Defined Date Not Working Correctly

English
#1

Greetings…

In my Calc spreadsheet, I have two columns configured to accept entered text as a user-defined date. In the first column, the user-defined date is to appear as YYYY-MM and the second column, the user-defined date is to appear as YYYY - all very straightforward and fundamental.

However, while the entered text in the first column appears correct as entered, such as 2024-08 or 2019-10, the entered text in the second column appears incorrect - regardless of the four-digit year date that I enter (2012, or 2024 etc), what appears is 1905. In the top input field, the incorrect dates appear as random months and days in 1905, such as 07/13/1905.

Any idea what is going on here? Any suggestions how to correct this problem?

Thank you very much for any assistance you might be able to provide. Take care…

#2

How did you set it up?

#3

Greetings, Mariosv…

Unfortunately, I am not sure what you mean by “How did you set it up?”. My spreadsheet consists of 14 columns and nearly 1800 rows. Two of the columns are intended to contain date information, with one column containing YYYY-MM date information and the other column containing YYYY date information. Both of these date columns are formatted with user-defined date structure.

What is “weird” is that the YYYY-MM column appears to be working as designed and intended, but the YYYY column appears to change the entered date to 1905 regardless of the four-digit date that I enter. Why 1905? The oldest year date that currently appears in my spreadsheet is 1950’s or later.

As a corrective action, I could simply creating a new YYYY date column next to the current YYYY date column, then delete the current YYYY date column, then format the new YYYY date column and hope that resolves the problem.

Naturally, I would rather understand what has caused this problem and what the appropriate corrective action would be, particularly in case I encounter this same problem again in the future.

Please feel free to ask additional questions. Thank you for your reply and willingness to help - most appreciated, to-be-sure. Take care…

#4

A date never has four digits!

2024-08-31 06 45 18
2024-08-31 06 45 18554×565 28.5 KB

#5

LibreOffice can recognize the entered Date VALUES based on some formats of textual date strings. Most of them are localised formats of the Dates.
The ISO format works with all of the locale settings.
This is the ISO Date format: YYYY-MM-DD

1950-10 and 1905-10 will be recognized as a pure string in my Hungarian locale Calc - if the cell is not preformatted with a Cell Style and with the Date Format Code: YYYY-MM
You must enter 1950-10-01, or you must preformat the input cells.

Can you upload your ODF type sample file here?

Dates.ods (15.0 KB)

#6

A date is actually stored as the number of days, since 30. December in 1899. So when you enter your 4 digits they are not recognized as dates, but stored directly. As we now, we have 365 to 366 days per year your number represents then some day in 1905 as illustrated by @PKG.
.
Change the formatting of your column to a number and your year is shown again.

#7

It is true that number format is used by Calc as a hint when converting the entered values to their numeric values (along with the date acceptance patterns). Such a conversion at entry, when detected that the string that user entered matches a date pattern, would calculate and actually store the respecting number (which, these days, is like 45535) instead of the user-entered string. However, this hint is limited, and there is no way to make LibreOffice convert a string that is a plain number (consisting only of digits, like “2024”) into dates (i.e., numbers like 45292 for 2024-01-01). Such strings will always be considered plain numbers, regardless of the acceptance patterns and number format.

However, indeed, if you only want to enter a year, you don’t need some special format for the cell - just input the four-digit number, and use it. The YYYY format is needed to display an existing date (as mentioned above, the number of days since configurable epoch) without month/day/time parts. If you have a date column A, and want to avoid new entry of the same year, you may use a formula in a cell to the right, as simple as =A1 in B1, and have column B formatted using YYYY format.

#8

Let’s agree that 2024-09 is not a date!
Therefore a NumberFormat code like YYYY-MM can’t be used as a date recognition pattern - neither for directly entered content nor for usage in formulas using DATEVALUE() e.g.

A cell containing a number representing an actual date based on the current NullDate setting may be formatted to YYYY-MM (or even YYYY), but in fact it will always contain a DD info. Using such a crippled format should therefore be ruled out.

What did I misunderstand?

#9

ISO 8601 considers that as a valid date (they have a broad range of dates, including year-only, year-and-week-only, etc.)

But indeed, when you work with Calc, you should realize that we don’t implement the whole ISO standard, and we only work with dates that point to a specific day in the history. Any format like YYYY that doesn’t show a specific day is just hiding that information, just like 0 hides any fractional part.

#10

Thanks for the clarification.
I should have added “except if there is a specification concerning the default (month and) day”.
Also this amendment is based, however, on the assumption that all these dates are represented as numbers (bad idea imo, but surely eternal).

#11

Nitpick: if Y-M is added to the date acceptance patterns (Tools → Options → Languages and Locales → General) then an input of 2024-09 is recognized as date 2024-09-01.
Caveat: if also M-D is present then order is significant, it should be M-D;Y-M, otherwise an intended M-D input would be accepted as Y-M. Still, two-digit year input <= 12 is of course not possible.

#12

Greetings, mariosv, PKG, Zizi64, Wanderer, mikekaganski, Lupp, erAck…

Please accept my sincere appreciation for the invaluable assistance and detailed information that each of you kindly provided me in response to my question regarding the cell date formatting problem that I encountered.

Based on the consensus contained in your very quick responses, I decided to reformat the two columns that contain YYYY-MM and YYYY as text, which appears to have restored the correct “date” information in these columns.

Again, thank you very much for your time and effort. Your detailed responses provided a learning experience for me, and for that, I am grateful. Take care…

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