[Solved] Autofill Date Series Pattern

[Moonwalker]

I need to auto fill a series of dates. Each date repeats exactly six times:

01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/02/2018
01/02/2018
01/02/2018
01/02/2018
01/02/2018
01/02/2018
01/02/2018

...and so on...

What is the most efficient way to do this? I've tried:

• Selecting the pattern and using the drag handle. That doesn't work. It screws up the pattern.
  i've tried filling manually as above, selecting the filled cells and using Edit>Fill>Down. That only recognizes the first row and fills the entire column with the one date.
  Iv'e tried Fill>Series but that doesn't seem to serve this particular purpose unless I'm missing something.

Thanks for the help in advance.

[Villeroy]

A1: start date
A2: =$A$1+INT((ROW()-1)/7)
drag down A2

[Moonwalker]

Duh. Thank's Villeroy. Believe it or not I have twenty years of experience with Excel and OpenOffice. Your solution is so obvious I'm kicking myself. Alas, age and brain farts sometimes cause the obvious (and formerly well known to me), to elude me by hiding in the dusty trunks of my mind's attic.

EDIT: Actually, sorry, no that doesn't work. I don't think a formula will serve as a solution for this since the first SIX rows must be identical, THEN the date changes for the next six rows etc. I don't' believe a formula autofill can even do that. Correct?

[Zizi64]

01/02/2018

What locale settings are you using (It is an american, or a german date?). It is better to use the ISO format of the date-time values (at least in this forum...):

YYYY-MM-DD HH:MM:SS

https://en.wikipedia.org/wiki/ISO_8601

[Villeroy]

Replace the 7 in the formula with a 6.

[Moonwalker]

01/02/2018

What locale settings are you using (It is an american, or a german date?). It is better to use the ISO format of the date-time values (at least in this forum...):

YYYY-MM-DD HH:MM:SS

https://en.wikipedia.org/wiki/ISO_8601

Not sure what you mean by "in this forum?"

Are you asking me what the date format is in my post? Or on the sheet? Either way they are identical, which is DD/MM/YYYY, which is the way it defaults to in the actual cell contents no matter what order you put it in. For example, I tried typing YYYY/MM/DD and Calc simply reverts it to DD/MM/YYYY.

Stupid question, because the bulk of my experience is with Excel, not OO, and there are some differences:

The format shouldn't make any difference should it?

[Moonwalker]

Replace the 7 in the formula with a 6.

Try it yourself. The formula returns #VALUE! I get the idea behind using a formula to solve this, but I honestly don't think that's possible. The formula would have to change for every cell you drag it into, which means the return value will change. When what I need is for the value to NOT change for six cells, THEN change for the next six, I do not believe there is any way to perform that pattern using a formula. I could be wrong, and would be more than pleased if you proved me wrong.

Thanks for trying though.

[Villeroy]

Try it yourself. The formula returns #VALUE! I

Nope, unless you put a text value in A1. Excel users lost the ability to distinguish between text and value some 15 years ago.

For us it is impossible to know if 1/2/2018 refers to the 2nd of January or first of February. Therefore we recommend using ISO dates in the problem descriptions. The actual formatting of your cells depends on the locale setting in the first place and does not matter at all. The only thing that matters is the cell value.
If you want to increase by one month every 6th row (your example counts 7):
A1: start date
A2: =DATE(YEAR($A$1) ;MONTH($A$1)+INT((ROW()-1)/6) ;DAY($A$1))

[Zizi64]

Villeroy's formula works for me:

Dates.ods

(13.58 KiB) Downloaded 159 times

You can format the dates as you want.

[Moonwalker]

SOLVED. There is actually a much simpler method that I had thought I had already tried but apparently not.

Also, yes, Villeroy's formula would work just fine probably, if not for the fact that I didn't account for row A being filled with column labels, so the first date is actually in $A2. Sorry. I have pretty bad ADD and often miss important details.

Here's what also worked for me:

DON'T select every filled cell. Rather, fill in the first twelve, then select ONLY the second set of six dates. Drag it down. Done. Awesome! Thank you both for your help. Even if I didn't end up using it, I just might next time around.

[Villeroy]

And what else? Do you increase by month or day? Does 1/2/2018 refer to January or February?

[Moonwalker]

And what else? Do you increase by month or day? Does 1/2/2018 refer to January or February?

Sorry, the date format I mentioned above is wrong. It is MM/DD/YYYY, increasing by day, same as in my OP. When I type ANY date into Calc, that is the data format it defaults to in the formula.

[Villeroy]

and this is why we prefer 2018-01-02 on this forum.

[Moonwalker]

and this is why we prefer 2018-01-02 on this forum.

Understood. But I find that a bit odd when that is not the way Calc itself actually uses it. It even changes it if you enter it that way. Point taken anyhow.

[Villeroy]

Calc and Excel do not apply auto-formatting. The program detects when a "special number" has been entered. A special number is a date, time, boolean, currency or scientific number. Then it applies either the number format that has been set explicitly for this cell. In case of no explicit formatting (that is number format "General") a particular locale specific number format for the detected type of value will be applied.

If the cell value is a number, the displayed cell text is not important. It may even change when you send the document to someone with another locale setting.
When I send you a date with no explicit formatting (number format "General" with cell locale "Default"), it will read 18.04.18 on my computer and 04/18/18 on yours but the cell value will be 43208 in any case. That is 43208 days since day zero 1899-12-30.

The one and only thing that matters is the value. You may tell us that your cell value is 2018-4-8, 8th of April 2018, April 8 2018 or even 43198 (the acutal value) but we can not be sure about the meaning of 4/8/2018 unless you tell us that this is a US date.

[Moonwalker]

Villeroy, I see now that some misunderstanding has been happening for sure. My use of the term "format" has led you to believe that I don't understand how cell formatting works. I do understand that though. When I referred to formatting, I meant the data format as in the way it appears in the formula bar, not the cell. From what you both are telling me, what Calc uses for dates in the formula bar is US format. What I see is MM/DD/YYYY. Even if I enter YYYY/MM/DD, it will change it to MM/DD/YYYY. This is why I thought everyone that uses Calc would know this is how it's used. But I see now that isn't the issue at all. It's simply a human communication problem, as you mentioned last. I ASS-umed you all would know what was meant because that is how Calc formats the data in the formula. You are telling me that doesn't matter if you are asking me what I mean.

Point taken. If I need any further help I'll try to remember. Many Thanks!

[robleyd]

what Calc uses for dates in the formula bar is US format.

This is the case IF the locale setting for AOO is English(USA) - see Tools | Options | Language Settings Languages for your settings.

In my case the locale is English(Australian); if I enter a date such as 12/ meaning day 12 of the current month, April 12, 2018 Calc will by default display dates in the cell as 12/04/18 and in the input bar as 12/04/2018. The greater part of the world uses the DD/MM/YYYY format.

[Moonwalker]

Right. Okay that makes sense then.

[Villeroy]

Rule of thumb: The formula bar shows the same date format as number format "General" would do but with 4-digit years.

For perfect confusion, every single cell and every cell style has a "language" setting in the number format settings which should be labeled "locale" since it overrides the global locale setting mentioned by robleyd. You can change the number format locale together with the input behaviour for every single cell.
The "Default" language/locale that is shown in the number format dialogs is the global one from the options dialog. And yes, it affects the formula bar as well.

Number format dialog with locale "Default - German (Germany)" following the locale setting in the global language options

When I write something numeric into an unformatted cell, the cell's language/locale reads "Default - German (Germany)" because that is my global locale setting and the cell input recognition behaves German with decimal 3,14, € currency by default, 31.12.1999 dates and more.
When I send that document to Moonwalker, the same cell gets a language/locale that reads "Default - English (USA)" and the cell behaves accordingly with decimal 3.14 and 12/31/1999. Same with the "Default - English (Australia)" except for the 31/12/1999 date.

If I want to look my cell content always the same, I need to switch to an "explicit language/locale" without the "Default -- " prefix.

This crude concept of "I don't care, just use the default language from my global setting" would not work well with currency symbols. My € must not switch to $ on your computer. Currency symbols are handled separately and do not change with the locale.

The spiral of confusion has another turn: numeric constants in a formulas always follow the global locale.
3,14 with a decimal comma changes to 3.14 with a decimal point when switching the cell's language/locale and you've got to enter any constant value in the same way.
However, the formula =3.14 always works with a global point locale and =3,14 always works with a global comma locale.
With a global locale "German (Germany)" and some English number format language/locale, I have to enter 3.14 as a constant value but =3,14 in formula context.

Since this started with dates: if you install LibreOffice, you can define which patterns should be recognized as dates but this does not make things any easier. It is just another source of confusion. For instance, this customization is reset to defaults when you switch the global locale setting.

[Moonwalker]

Don't mind me. Remember I have ADD.

Actually Villeroy, I do have one question about the formula. I did a little messing about with various data types in similar patterns (repeating a certain number of times then increasing or decreasing by a certain increment every so many times). The method I used above (viewtopic.php?f=9&t=93262#p443456) did work for my purposes but it doesn't work for other data types. The formula you posted DID work. Here's where your desire to explain in detail would be extremely helpful. Can you explain the logic behind the formula? I understand most of it but I am confused by why the "/N" portion at the end dictates when to change the increment instead of simply performing a division operation. Obviously there is something about formulas I never realized, which explains why I thought it wasn't possible to do this using them.

[Villeroy]

Just enter =ROW()-1 into a cell of a blank sheet, say A1.
Then INT(A1/6) into B1 and drag down both formula cells.
This is the value we add to the start value. Since all dates in all spreadsheets are day numbers, this is the amount of days we add to the start date.

[Moonwalker]

Okay, yes I understand how it is done. I guess what I'm asking is why it works. Normaly A1/ 6 would mean [value of A1] divided by 6, where if A1 value = 6 then the formula =A1/6 would return 1. What I'm trying to understand here is why, in this instance, it's actually telling calc how many times to repeat the original value.

[Villeroy]

Sequence.ods

Sequece x times 0, 1, 2, 3, ...
Sequece 0, 1, 2, 3, ... x

(15.23 KiB) Downloaded 119 times