Calc: How to Automatically Fill Cells With Sequential Dates

[DinoNerd]

This may be a FAQ, but the answers I’m finding aren’t helpful.

What I want is for each row I add at the bottom of the active part of my spreadsheet to have a cell contain a date one day after the one above.

In Numbers, I’d make sure the first row contained a date, and make sure it had been recognized by the spreadsheet as a date. I’d add a row below it, and put in a formula like =A1+1 Numbers knows I want the next date in sequence. Calc gives me #VALUE!

Each time I added a row below the one with the formula, the same formula would be applied, referring to the cell from the row above (I didn’t fix the row number in the formula.)

If the cells already existed, I’d block copy the formula to all of them.

After finding this was a dead end, I looked in this forum.

The first possibility I saw was:

Fill all 365 days in 365 cells automatically

i wanna all 365 days in 365 cells automatically or take for example cell A1 with 01.01.1999 and next cells in A column should take A1+1 but so that i dont have to manually type in =A1+1, it should take A1 and automatically change it to A2 and so on

The solution there was

Enter the first date in the first cell of the series. Click in another cell. Grab the little black handle on the bottom right of the first cell with the date in it and drag it down as far as you need. This is called Autofill - see the Help. For other ways, see filling;cells, automatically in the Hel…

I didn’t see a little black handle, but there might have been a tiny blue dot at the lower right hand corner of the cell containing a date. I dragged it to the cell below, and got a date one year after the cell above.

Maybe this would work if I formatted my dates as YYYY/MM/DD - i.e. it would increment the last field? But that’s not the format I want.

This answer suggests I may have to format the date in the ISO date format. (I’d have to look up what that is.)

Fill a large range of cells with sequential dates

The examples I’ve seen indicate that using sheet—>fill should open a dialog box for me to enter a start date, end date and increment value. If I enter a date value in a cell and select a range of cells, all I can do is fill the range with the same date. No dialog box ever opens. I could enter a start date and drag down, but this would take quite a while to fill 53,600 rows when I should be able to do this automatically.

These were the only two possibly relevant answers I found, though I suspect there are more. They aren’t working for me.

How do I get a consecutive series of dates in consecutive cells of a row/column, without typing every single one of them manually?

Bonus question: What if I want a series of consecutive Sundays (+7 to the previous date)? Second bonus question: How about consecutive month/year pairs (with no day of the month?)

[DinoNerd]

I found Fill Series which looked promising. But while it figured out I wanted dates, that’s all it’s gotten right so far. It mostly reports “invalid value”, but I did once get it to fill in actual dates - unfortunately all the same.

[robleyd]

Is your “date” a valid date, or just text that looks like a date? Use View | Value Highlighting to see.

And/or you could upload a sample file that demonstrates the problem. Don’t forget also LO version and OS information.

See also Automatically Filling in Data Based on Adjacent Cells