CALC unable to autocomplete date series yyyy/mm/dd?

[icerabbit]

I need to track some dated numbers across many years and figured I could do a simple sheet per year, 2000, 2001, 2002, etc with the calendar dates 365 cells vertically down column A, 2000/01/01, 2000/01/02, 2000/01/03, etc.

Formatted column A to Date YYYY/MM/DD

Filled in the first three cells, tried to autocomplete and LO goes

2000/01/04
2000/01/05
…
2000/01/31
2000/01/32
2000/01/33
…
2000/01/125
…
2000/01/365

Not quite working as intended, I think.

Autocompleting the date series seems to only work in the default dd/mm/yy format, but as my brain is not wired with the American date format, that is not really desirable to me. But, I suppose I will be forced to go that route and then afterwards, after having built all the years, reformat the date columns.

[mikekaganski]

What format is “YYYY/MM/DD”? ISO 8601 doesn’t mention that. Did you try with a proper YYYY-MM-DD?

(Anyway, your data looks to be text, not date, which makes cell formatting irrelevant. If it contained proper dates, then formatting YYYY/MM/DD would still produce what you wanted.)

[anon73440385]

Please read what a date is: OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 - Part 2: Recalculated Formula (OpenFormula) Format and note that formatting a cell to your notion of a date doesn’t influence in any way the date recognition algorithm of LibreOffice.

[ve3oat]

No, it does not conform to ISO 8601, but sometimes one has no control over the format.

I have a data acquisition device that produces CSV data files with dates in YYYY/MM/DD format and until sometime last fall Calc accepted the dates without any problem. Ever since the change (Calc update?) I struggle every week to get my Calc spreadsheet to correctly interpret the weekly thousand-plus data entries.

[anon73440385]

Filled in the first three cells, tried to autocomplete and LO goes

…but OP stated, he manually added the first few cells and my urgent advice (to prevent these endless date format questions here): Regardless how you have (date-) formatted your cells, use YYYY-MM-DD when it comes to enter a date

[erAck]

The result rolling over to 2000/01/32 indicates your data is Text instead of numeric Date type, hence the trailing number is incremented. Convert the text content to proper numeric date content, see this FAQ.

[icerabbit]

Sorry. I was not aware of any particular date format being a standard, nor did I suspect it; as I grew up with a / in or a . in dates being the norm; entering three dates that way and autocompleting has worked forever for me in MS O; LO contains multiple examples with / in the date format list … and so I figured that even if LO is prone to considering everything that is not a number as text; if I formatted the cells as date and then typed the dates as desired adhering to that structure, LO would actually know that I am entering a date … and not text. Seems it does not. But, anyway.

I learned something new, thank you.

Not everybody around the world knows about the dash. Clearly some regions may have missed that memo and still adhere to their tradion and upbringing.

Date format by country

Dates with - is perfectly fine to me.

YYYY-MM-DD works fine for data entry, it does autocomplete, and is perfectly usable for my purposes.

Thank you all for your prompt contributions!

[erAck]

Whether an input of yyyy/mm/dd is accepted as date input depends on the locale and its date acceptance patterns, see Tools → Options → Language Settings → Languages. Your locale may contain M/D/Y or D/M/Y, but that does not mean Y/M/D would be accepted as well (unless explicitly listed). The ISO form of yyyy-mm-dd is accepted in all locales.

[richardm1]

Have you tried customising Date acceptance patterns in Options? I was having similar issues having been used to the convenience in openoffice of being able to enter 13/ and have autocomplete to 13/12/2021 or 3/2 and have that autocomplete ro 3/2/202. I removed D-M and added D/

You might find something useful here.

[DRH]

Hi,
I had this same problem and it was very irritating. I tried all of the suggestion and still it didn’t work. I seem to have got the problem fixed and I believe this is what caused my problem.
I created a spreadsheet and used column A as the date.
I used (month/date/year), (05/15/2022).
When I tried to use auto-fill going down it changed 05/15/2022 to 05/15/2023.
This was caused by a procedure error on my part.
I discovered that the cells have to be formatted as a DATE before any data is entered.
I hope this is helpful.

[erAck]

Cells don’t have to be pre-formatted as Date, but they must not be pre-formatted as Text or the input otherwise not be matching the locale’s date acceptance patterns (Tools → Options → Language Settings → Languages). For example, if your locale uses DD/MM/YYYY date and D/M/Y date acceptance pattern then entering 05/15/2022 will not be a valid date and the cell content will be text. Incrementing such text increments the rightmost number, if any, which is 2022 → 2023.

[DRH]

Thank You. I am not disputing your info. As I stated earlier it was a procedural error on my part. I was just relaying what I discovered on my system. So, On my system when I open up Calc it comes up with cell format General Number. Perhaps I had copied and paste, I’m really not sure. This what I found.
I had several lines of what appeared to be dates (02/05/2022) in column A. When I tried to auto fill it just added 1 to the year date. I then selected Column A, Cell Format. Selected the desired date format, (02/05/2022)
Everything appears fine but it still will not auto fill when I drag the square down it still adds 1 to the year date. When I check the cell format it displays DATE with the desired format. To correct the problem I had on my system I had to retype the top date and then I could correctly auto fill by dragging down.
On my system I don’t have any of the problems you are referring to.
With the date format set to DD/MM/YY I can type the date in as 5/7/22 and Calc enters it as 05/07/2022; While it appears in the cell as 05/07/22 in the formula window it shows 05/07/2022. If so desired I can select the column or a cell, go to cell format and select YYYY/MM/DD and Calc will automatically make that adjustment. The one thing that I can not do is change it to DD/MM/YYYY. Perhaps that is where the local language format comes into play?

[erAck]

You never mentioned your locale nor the date acceptance patterns. You can set the display format to anything you like, including DD/MM/YYYY, but what input is accepted as date depends on the date acceptance patterns. If the input does not match any pattern and the actual cell format contradicts those but the input matches that then the cell format’s pattern is considered last, which may or may not be the desired result.

[DRH]

You are correct and if the data in the cell appears correct but cell format is wrong when you auto fill copy down you also copy the wrong format. When something isn’t working correctly it is very frustrating. Perhaps this will be helpful to other people who are having a frustrating day. Thanks for the discussion.