Ask Your Question

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

asked 2020-03-05 15:07:07 +0100

icerabbit gravatar image

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.

edit retag flag offensive close merge delete



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.)

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-05 15:09:50 +0100 )edit

Please read what a date is: and note that formatting a cell to your notion of a date doesn't influence in any way the date recognition algorithm of LibreOffice.

Opaque gravatar imageOpaque ( 2020-03-05 15:33:14 +0100 )edit

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.

ve3oat gravatar imageve3oat ( 2020-03-05 15:36:47 +0100 )edit

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

Opaque gravatar imageOpaque ( 2020-03-05 15:43:51 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-03-05 16:57:01 +0100

erAck gravatar image

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.

edit flag offensive delete link more

answered 2020-03-05 19:37:59 +0100

icerabbit gravatar image

updated 2020-03-05 19:48:41 +0100

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.

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!

edit flag offensive delete link more


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.

erAck gravatar imageerAck ( 2020-03-08 17:55:04 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-05 15:07:07 +0100

Seen: 223 times

Last updated: Mar 05 '20