Calc: format date YYMMDD, paste YYMMDD data, extend YYMMDD cells

English
, , ,
#1

I want to work with dates in the format YYMMDD in Calc. For example the date 2021-03-26 is 210326 in the YYMMDD format.

I can create a custom format in Format cells… → Date → Format → Format Code → YYMMDD.
But if I have the YYMMDD date “210326” on the clipboard and paste into a Calc cell already set to YYMMDD format then Calc transforms the cell to “751106” and the value in Calc’s “URL bar” (not sure what its proper name is) is “2475-11-06” - not what I want.

I want the value in the cell to be “210326” and either “210326” or “2021-03-26” in the URL bar.
In other words I want Calc to parse the input “210326” as a date value. Calc already does that for input strings with the form “2021-03-26”, I want to customize that parsing for cells that already have my user defined YYMMDD date format.

If I instead set the cell format to number then “210326” can be pasted without changing, but then I can’t click and drag to extend the dates to cells below correctly. The values extend as numbers with +1 with no regard for calendar dates, for example after 210331 the next value should be 210401 (April 1st) but with number format the cell becomes “210332”.

image description

Zero date in Calc, metaquestions
#2

I want to work with dates in the format YYMMDD
Never do so. Don’t invent another stubborn and ambiguous date format, but either use ISO 8601 YYYYMMDD or ISO8601 extended YYYY-MM-DD better readable for humans. Two-digit-year-dates are “slightly crminal”. This is particuarly important if dates may get exported after conversion to text.

1 Like
#3

Sorry - but an image sadly doesn’t show the real nature (types) of your data. Please upload that as real ods file but I’m pretty sure that 210326 is an integer value and added to a date cell correctly yields 2475-11-06, which is the 210326th day after 1899-12-30 and that’s what dates are - the number (integer!) of days after that date.

#4

@anon73440385, my question already has all the information there is. I'm pretty sure that 210326 is an integer value and added to a date cell correctly yields 2475-11-06 Yes, I have 210326 on the clipboard and want to paste it into a cell and (1) have Calc recognize it as a date value and (2) have Calc display the date value with formatting YYMMDD and (3) have Calc’s click drag expand to expand values to the cells below as dates also with formatting YYMMDD. You say that it correctly yields 2475-11-06 but it is that behaviour that I want to work around in this case. Is there no way to do so? That is, is there no way to get Calc to interpret 210326 from the clipboard as a date value (when the cell already is formatted to display date values with that format).

#5

@Lupp Don't invent another stubborn and ambiguous date format. I also don’t like the format, but that opinion is irrelevant to this question. The client data (input and output) I have to work with already has the YYMMDD format and I cannot change that.

#6

Is there no way to do so?

you want the impossible and contradictory


> (1) have Calc recognize it as a date

That requires Calc getting an integer value or is able turning a given text on input ( based on your locale’s date acceptance pattern) into an integer. That’s fulfilled and Calc has a date.

But you want that integer interpreted as text (based on some format) being turned into another integer (dates are always integers in LibreOffice Calc) you defined for that interpretation and in that sense you want to influence the number recognition algorithm. That’s impossible.

If you want to turn an integer (based on you own interpretation of that imnteger) into another integer, the only way would be through a helpers column and using a formula which reflects your intention (e.g: =DATE(LEFT(VALUE(A1);2);MID(VALUE(A1);3;2);RIGHT(VALUE(A1);2)) and formatting cell using Format Code YY/MM/DD assuming A1 contains integer [e.g. 210326])

#7

@anon73440385 I’m new to Calc, apologies if my questions are confusing. The external data I work with is in plaintext and will be put on the clipboard in Windows 10.

the only way would be through a helpers column and using a formula … and formatting cell

Thanks. With the change =DATE((2000+MID(A1;1;2));MID(A1;3;2);MID(A1;5;2)) I can paste 210326 into A1 and get a date value in the formula’s cell. With user defined format YYMMDD the date value is shown as 210326. Wish (1) and (2) done! But not (3) - I can’t drag to expand the formula’s cell to incrementing date values. Do you know a workaround?

I am now aware of the “Date acceptance patterns” [DAP] setting. Pattern YYMMDD there would solve my issue. But DAP doesn’t allow that. Perhaps because DAP is global and pattern YYMMDD would mess up 6 digit numbers in other cells not inteded for date values. I wish for an option to set local DAP overrides for a user defined cell format.

#8

This is not related to date acceptance patterns. Your input is a number. There is no way such implicitly assumed YYMMDD layout of an integer number is recognized as date input.

#9

@erAck No, YYMMDD is a pattern just like YYYY-MM-DD is a pattern. Calc’s “date acceptance patterns” setting just happens to disallow the former pattern, but that could change. Is 2021-12-13 a date or one number subtracted by two other numbers? Answer: depends on the parser. Ditto for YYMMDD.

#10

@bikerosefive Yes, YYMMDD is a number display format pattern. No, it is not an input mask pattern.

#11

@erAck What is an input mask pattern? The term is not in the LO documentation index. I suspect you misunderstand my above wish for a local Date Acceptance Patterns override to accept YYMMDD. I know that feature is not currently available. I wish it was. It is technically possible to implement in software.

#12

That term is not in the help because there is no such thing. Your wish is that a number display format of YYMMDD would recognize a 6 digits input as a date instead of a numeric value. Number formats do not act as input masks, an input of 6 digits is a numeric value, regardless of how the cell is formatted for display, like with any other date format. Adding YYMMDD to date acceptance patterns makes no sense because date acceptance patterns describe what input is automatically recognized as date, regardless how the cell is formatted for display (unless it is formatted as Text), hence a date acceptance pattern has to include separators. If adding YYMMDD as date acceptance pattern would work like you wish then it would not be possible to enter a 6 digit number in the spreadsheet anywhere without it being transformed to a date. Certainly that is not desired.

#13

@BikeRoseFive: A next attempt:
If you need - for saving your life - a feature at least mimicking something like what you described, you must accept two facts and a warning:
-1- The entered “thing” must first be of type text after Enter.
-2- The secondary recognition (and checking hopefully) requires custom code to run.
-3- There may occur issues again and again depending on the way the tool is (mis) used by whomever.

Much better would be to format your extremely-bad-dates-column with the code @ (no number-recognition), and to omit the idea of automatic incrementation with FillAuto. I don’t remember a statement concerning the question if the “date-like strings” must be usable as “actual dates” when referenced by formulas for calculations. To enable this you would need slightly complicated formulas, but that’s at least maintainable, and not completely against the grain of spreadsheets.
But why shouldn’t people enter dates in a sensible format?

#14

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.

VeryBadDateFormatting.ods
VeryBadDateFormatting.ods

[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 Text
If 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 44281.
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.
[/Edit]

#15

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.
I also get the correct incrementation if I set a cell to date format and paste a string like 2021-03-26. But my goal is to paste strings like 210326.
If you actually entered the number 210326 there, you cannot expect a result different from waht is shown.
What does cannot expect mean? Are you saying that you’re certain Calc cannot be set to parse an input with the form YYMMDD as a date value? Or are you saying something else? Calc must already contain a parser that detects the form YYYY-MM-DD in input and, if the cell has date format, treats that input as a date. I’m asking if there is a way to customize that parsing.

#16

There are means, of course, to interpret the number 210326 or the text (string) “210326” as a date, But cocerning the number there is definiely no means to do this by default or based on a setting when filling (using FillAuto) . Concerning the text (and any input is a text -a sequence of keystrokes- as long as it isn’t “recognized” as a number or a “special number”) such a “date acceptance pattern” can’t currently be set, but would be possible in principle. It would of course thwart any attempt to enter most of the “ordinary integers” with 6 decimal digits.
Concerning the format YYYY-MM-DD the recognition as a date is implicit because this is the one and only globally specified, human-readable, and not number-conflicting date format (ISO 8601 extended).
Concerning the “date acceptance patterns” you can define under language settings, delimiters are indispensable as now should be clear.
OMIT THE SILLY, AMBIGUOUS, And MISLEADING format you are talking of!

#17

Concerning how to get rid of your VeryBadDateFormat and how to convert numbers you entered in the hope to get them interpreted as dates to actual dates (represented by numeric values the spreadsheet way) you can refer to the new attachment VeryBadDateFormatting2.ods.
If you insist on continuing with YYMMDD ask someone else, please.
Over.

#18

@Lupp As I said in a previous comment I also don’t like the format, but that opinion is irrelevant to this question. The client data (input and output) I have to work with already has the YYMMDD format and I cannot change that. Generally if someone asks “How can I do X?” replies of the form “I don’t like X, X is very bad” are off-topic. Your ALL-CAPS BOLD! off-topic expression of dislike lowers the quality of your reply.

#19

“Omit the silly, ambiguous and misleading format you are talking of!” {YYMMDD} is good advice.
I don’t know the type of relation between you and the “client”, but clients dong things in a basically bad and probably dangerous way should get advice concerning the facts. …In a very polite way, of course.

#20

@Lupp, bye

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