If I format a number supposed to mean a date by the code
YYMMDD, and then fill down using the menu path or dragging with the mouse, I get the correct incrementation. The increment actually is one, but not for the formatted content of the cell just looking like an ordinary number, but for the actual content which then will be formatted as is set for the cell.
Check if the first cell actually has the mentioned format, and if the process of filling down also extended this format as it is standard behaviour. If you actually entered the number 210326 there, you cannot expect a result different from waht is shown. Images don’t allow the needed distinction. Therefore always attach real examples (as
.ods in this case).
See attachment for an example.
[Edit 2021-03-29 about 18:00 UTC]
Quoting @BikeRoseFive: “The client data (input and output) I have to work with already has the YYMMDD format and I cannot change that.”
I don’t clearly understand what’s meant by "input and output here. What’s the means of interchange? Do you get/deliver Calc spreadsheets or something else?
I still don’t know for sure if the so-called data are supposed to be of type
Number or of type
Number, formatted by
YYMMDD, that’s very bad for many reasons, but as long as the sheets between which such “data” are exchanged have the same
NullDate setting, it’s manageable. We must know, however.
If a number meaning the date
2021-03-26 is shown in that “date format” as
2103026, the actual numeric content (or result) of the cell is
If the actual numeric content/result of a cell is
210326, the meaning as a date is
2475-11-06 independent of what format is chosen for the display.
Entering (typing) the number
210326 will never result in a recognized date as long as the standard processes and supported settings (date acceptance patterns) are used.
To change the behaviour for specific cell ranges insofar is possible based on custom code , but no sound “client” would accept sheets depending on such queer means.
Clients and their agents/employees/consultants need to accept the neccessity of unambiguous, clear and safe ways of communication.
The only actually safe way to communicate dates is to produce them as texts following
ISO 8601 extended.
YY formats, and in specific those without a separator or with slashes are definitely proven to be ambiguous. Exchanging them as numbers the way they are represented in spreadsheets (unformatted) is dangerous due to their depending on
NullDate settings as already mentioned.
Nobody can change this kind of facts.
Facts and their understanding are essential when using spreadsheets. Good manners are nice. When using a kind of forum, part of them is the questioners effort to tell everyting relevant for the problem from the beginning.