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.
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.
Be sure they are dates not text dates?
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.
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!
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.
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.
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, …
I would still assume my answer actually hit the question as the comment on @mariosv 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.
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).
-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!