Ask Your Question

"Years" section of date advancing instead of "days" section when dragged? [closed]

asked 2018-03-25 16:16:37 +0200

appreciatethehelp gravatar image

Hello, when I drag the corner of a cell containing a date to produce more dates below, the "years" section of the date advances by one for each cell it is dragged into instead of the "days" section.

All cells (including the one containing the original date) are formatted for date and formatted DD/MM/YY.

I haven't had this problem in the past so am unsure what has caused this.

Thanks for any help.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-05-01 20:34:37.031618

3 Answers

Sort by » oldest newest most voted

answered 2018-03-25 22:03:02 +0200

Lupp gravatar image

updated 2018-03-25 22:13:40 +0200

Quoting @appreciatethehelp: "What's the difference?"
The difference is the contenttype of the cell. If that type is 'Text' (forced by format code "@" e.g.) and you see a "date" like "12/07/2017" the cell actually contains these 10 characters. Filling this down the incrementing process for text will apply, meaning that the final numeric part (that's "2017" in the example) will be incremented, and in case there is none at the end, the starting numeric part will play the role. Actual dates as specified for spreadsheets are, however, numbers. In the case of the example the content of the cell having a content type 'Number' with subtype 'Date' (only represented by an appropriate format code) will be the number 42928 (the 42928th day after 1899-12-30) though the display may show "12/07/2017" or whatever is generated due to the chosen Numbers format code. In this case the automatic incrementing for numbers will simply add one per step, meaning the next day, and the increasing values will carry to the months and finally to the years independent of the specific chosen format.

edit flag offensive delete link more

answered 2018-03-25 18:46:37 +0200

m.a.riosv gravatar image

Be sure they are dates not text dates?

edit flag offensive delete link more


What's the difference? What I have done is formatted the cells to date DD/MM/YY, then in the first cell (which the others are "copied" from by clicking and dragging the corner) entered today's date like so: 26/03/18. What then happens is the following "copied" cells display 26/03/19, 26/03/20 etc.

appreciatethehelp gravatar imageappreciatethehelp ( 2018-03-25 19:00:07 +0200 )edit

I have never used that method to copy but it sounds like the "copy" process includes some sort of implied formula that is automatically incrementing in each successive cell. Maybe it is meant to be that way. Have you tried using Copy and Paste-Special instead? (Under the Edit menu button.) Make sure that only Numbers and Formats are selected for pasting!

ve3oat gravatar imageve3oat ( 2018-03-25 20:19:46 +0200 )edit

Text (instead of Date) content may be produced if the cell is already formatted to Text (see answer by Lupp) or the DD/MM/YY sequence entered does not match how your current locale represents a date and/or its date acceptance patterns. See Tools -> Options -> Language Settings -> Languages, Locale setting and Date acceptance patterns.

erAck gravatar imageerAck ( 2018-03-26 14:54:24 +0200 )edit

Aha! going to Libreoffice > Preferences > Language Settings > Languages as suggested and changing the locale settings to match my current location fixed the issue. Cheers! If you post this comment as an answer to the original question I'll mark it as the correct answer then close the question.

appreciatethehelp gravatar imageappreciatethehelp ( 2018-03-31 14:47:58 +0200 )edit

answered 2018-03-26 14:24:17 +0200

erAck gravatar image

To increment by years (or months) instead of days you need to select two cells that differ by one year (or month). For example, A1 23/11/2017 and A2 23/11/2018, select A1:A2 and pulling down yields 23/11/2019, 23/11/2020, ...

edit flag offensive delete link more


I would still assume my answer actually hit the question as the comment on @m.a.riosv by the OQ with its additional question made clear to me. The attempt here to answer the question assuming the dates are numbers is missing the question, I would judge.
@appreciatethehelp: Please clear things finally! We are wating our time otherwise.

Lupp gravatar imageLupp ( 2018-03-26 14:42:56 +0200 )edit

Ah right, seems I misread the question. If it already increments by year with a DD/MM/YY "date" with having only one cell selected then likely the cell has content of type Text instead of Number(Date).

erAck gravatar imageerAck ( 2018-03-26 14:47:36 +0200 )edit

-For Lupp, from my comment above- "Aha! going to Libreoffice > Preferences > Language Settings > Languages as suggested and changing the locale settings to match my current location fixed the issue. Cheers!"

Thanks for all the help guys!

appreciatethehelp gravatar imageappreciatethehelp ( 2018-03-31 14:50:26 +0200 )edit

Question Tools

1 follower


Asked: 2018-03-25 16:16:37 +0200

Seen: 651 times

Last updated: Mar 26 '18