Calc: How to Automatically Fill Cells With Sequential Dates

English
#1

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:

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

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

#2

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.

#3

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

1 Like
#4

I suspect it isn’t, but I can’t tell.

Here’s a picture of part of the spreadsheet with value highlighting on. I don’t know what would tell me the data type in the opinion of the software.

Screenshot_20260220_110142
Screenshot_20260220_1101422886×1828 419 KB

Note the lines above, with dates in a similar format but with a 4 digit year. This does not appear to be a legitimate date format in Calc - at least, I cannot use Format - Cell - Numbers - Date to display dates that way. If I want a 4 digit year, I have to spell out the whole month, or display the date in entirely numeric form.

In Numbers I believe I can change the software’s idea of a field’s data type with Format - Cell - Numbers - Date, unless the requested conversion is impossible (“hello” cannot be interpreted as a date).

I tried this in Calc, and the net result was that when I clicked on a numeric or date cell, it had a leading " or ’ (I forget which now) in editing space at the top of the screen. If I edited each one individually, they began behaving like numeric values. Or at least they became right justified rather than left justified, which seems to be a side effect of being seen as a number by Calc.

Related to that, is there a better way than retyping cells individually to convert them from strings-that-look-like-numbers to things-usable-in-calculations? If not, I foresee extreme pain every time I generate a CSV programmatically, then import it into Calc to do something interesting with the contents.

#5

Libre Office 25.8.4.2 (X86_64)
Kubuntu 25.10

#6

I went back to editing the spreadsheet with today’s information. As it happened, this involved typing the characters 24:49 into a cell that may or may not have been told to use time format in the past. It was converted into 24:49:00 - right justified and in blue. I’m not sure what Calc thinks I meant; the extra :00 makes the value meaningless to me. (Does it think it’s a date?)

Typing 24:49; resulted in Calc leaving the value as typed.

Screenshot_20260220_113048
Screenshot_20260220_113048696×242 50.3 KB

I suspect the blue is the result of View | Value Highlighting

If that’s how a numeric value is indicated, then this new weird string is the only numeric value in the whole spreadsheet.

FWIW, this is close to bug-for-bug compatible with Numbers. Typing what looks like an invalid hh:mm time in Numbers, without a trailing semicolon or similar, results in numbers misinterpreting the typed value as a date, and changing its format to make that clear. I’ve never been able to find a way to turn that off, so have trained myself to add a trailing semi-colon when I want the “time” interpreted as a string. So I can hardly complain if Calc does the same thing with its input.

Yep, this IS likely. Typing a valid time - 11:11 - resulted in Calc changing the value displayed to 11:11:00 AM, all in blue - requiring twice the cell width to be visible.

I can defeat the display change with format - cell - numbers - time. Of course I’ll have to apply it to all columns where I might type something that looks like a time. (As it happens, most of the values in these fields are actually times. I use faux times like 24:49 to indicate e.g. that something that was supposed to happen on Tuesday happened 49 minutes after midnight.)

#7

You might find this useful, Faq/Calc/How to convert number text to numeric data

I don’t think it is a bug, sometimes you data to look like a number or date but don’t want it to be included in calculations

#8

From the HELP

By default:

Text cells are formatted in black, formulae in green, number cells in blue, and protected cells are shown with light grey background, no matter how their display is formatted.

#9

What about
increment_decrement_by_dragging.ods (23.9 KB)

Wherever sufficient I would prefer deault horizontal alignment over “Value Hioghlighting”.

#10

Thanks. I feel like a complete idiot, and probably look like a worse one. In my defense, this distro managed to give me a LibreOffice package where the help doesn’t work, giving errors like

Firefox can’t find the file at /usr/share/libreoffice/help/index.html?Target=scalc/.uno:HelpIndex&Language=en-US&System=UNIX&Version=25.8.

I first thought the help was packaged separately - debian derivatives often do that - and tried looking for a separate package.

But I have libreoffice-help-en-us and it’s not helping.

FWIW, ls reports that /usr/share/libreoffice/help/index.html exists and is world readable.

#11

Just what Calc increments is variable, without a clear pattern. It increments what it thinks a user in your situation would want. AFAIK there is no way to specify what it wants.

#12
  1. In column A, type in the number 1, then drag it down to create a column of 1,2,3,4,5,6…
  2. In column B type in your start date, then ctrl-drag is down to fill the column with the start date
  3. In column C type in =B1+7*A1, then drag it down to fill it with weekly increments
  4. Select column C, then Data > Calculate > Forumla to Value

xx.ods (12.6 KB)

#13
  1. In column A type in the start year, drag down to create a column of years
  2. In column B, type in =TEXT(A21 & “-01-01”,“YYYY-MM”), then drag down to get year/months
  3. Select column B, then Data > Calculate > Formula to Value

xx.ods (12.4 KB)

#14

FillSundaysCalc

2 Likes
#16

The way how Calc fills cells sequentially depends on the cells being number or text. Possibly, it is some kind of “fuzzy logic”. However, it is the same logic since 3 decades (when I used Excel 7). Spreadsheet applications like Calc or Excel follow the same simple concepts and rules since the 90ies. All these products distinguish between numbers and text. The text “2/3/2026” is a completely different category of cell value than the formatted number displayed as “2/3/2026”. In this respect, a spreadsheet is just a simplified programming language with 2 or 3 data types.
In Calc Ctrl+F8 highlights constant numbers with a blue font color. A green font indicates formula results. In case of formulas, concatenations and text functions return text.
=ISNUMBER(A1) reveals if A1 is a number or not.

Any attempt to convert textual dates into numeric ones may ruin your data. Preferably, you would import correct data that do not need any retroactive fixing.

#18

Yes, a lot of my problem is that these heuristics are different in different spreadsheet programs, or at least different between Calc and Numbers. (For all I know, they are identical in Excel and Calc; I haven’t used Excel for about as long as you.)

Numbers regularly annoys me by doing what I don’t want, such as mistaking character strings with numbers for dates, and reformatting dates into some longer, generic format when I try to edit them, making it easier to retype from scratch to edit a date. But I’m familiar with its quirks, and find it relatively easy to work around them.

Calc has different quirks. I’m not familiar with those quirks, and a lot of what I’m trying to do in this forum has been to get an ultra fast lesson in how to work around quirks I don’t even know. Add to this a job lot of spreadsheets converted from Numbers, and designed to work comfortably with Numbers, and I’ve given myself a recipe for a lot of pain by deciding to abandon MacOS in favor of Linux.

It would be nice if both tools documented such things as the heuristics they use to distinguish numbers from text, and ordinary numbers from dates. They don’t, though Calc does a better job than Numbers - and you can easily determine which things it considers to be numbers, once you know about ctrl-f8.

One of the differences seems to be that Calc makes a stronger distinction between “:what’s in this cell” and “what formatting has been set for this cell”. If Numbers has a way to say “make all these cells numeric” other than “format them all as numbers” I don’t recall it.

#19

I’ve already filed one bug against Calc for not correctly handling COUNTIF() when importing a spreadsheet directly from Numbers.

That leaves the alternative of importing data by having Numbers export it in some other format, and seeing whether Calc can correctly import that format. So far, when using excel format, I find the appearance of the spreadsheet transformed in ways I don’t like, but I haven’t (yet?) found an outright bug, where the underlying data (including formulae) is not passed through the chain correctly.

The third choice is to pass data by CSV. That destroys all the formulae, but does the least to mess up the spread sheet’s appearance. I’d hoped it would be viable for spreadsheets totally lacking formulae, even though CSV passes everything as text. But as it turns out, Numbers has heuristics to recognize numbers in CSVs, and/or such a simple method to convert cell contents to numbers that I used to routinely generate CSVs in Python, and import them to Numbers to manipulate the data. Calc, on the other hand, didn’t have an obvious-to-me way to do that. (I think I eventually found it, but haven’t checked how well it works. Your warning here makes me suspect I’ll find problems.)

This whole thread is the result of importing a spreadsheet via CSV where the only formula was that each sheet had a header column containing systematically increasing dates. Unfortunately the date format I used in at least one of the sheets turned out to be one Calc is unwilling to display, and so is probably also unwilling to recognize.

Bottom line, I fear it’s not possible to import correct data, and certainly isn’t possible to be certain one’s data has been imported correctly, except via a process approximately as tedious and time-consuming as retyping the whole thing.

I suspect Calc will be a lovely tool to use, for spreadsheets originating in Calc. I wish I were already at that stage.

Meanwhile, my next experiments will involve importing via excel format, and then fixing up the appearance to the extent I can.

#17

For real automation you need either a

  • readymade tool of the software which does exactly what you want or can be configured to do so.

OR a

  • “taylor made” tool implemented by user code.

If the first option isn’t available, and the second option isn’t acceptable for you due to its disadvantages, you either can

  • simply do the additional interactive work needed due to the fact that existing tools aren’t shaped exactly for your purposes

OR

For the second way you need to be very patient (some years e.g.) and/or with a WONTFIX by QA.

For any problem related to dates it is a sin to use a stubborn “format”. Only ISO 8601 (delimited) is reasonable. That’s YYYY-MM-DD .

#20

Here’s what I’ve learned so far:

  1. It’s not obvious what Calc considers to be numeric, and what Calc considers to be a string. Use View|Value Highlighting (aka ctrl-F8) to find out which you have. (Things Calc considers to be numeric will be blue, and this includes dates.)
  2. Numbers has date formats that Calc won’t generate, and presumably doesn’t understand. In particular, Sun, 1 Feb 2026 is not a date. Sun, 1 Feb 26 is a date, and so is Sun, 1 February 2026.
  3. Formulae like =A1+1 do work for dates, if calc believes they really are dates.
  4. You can drag such a formula downward and get a consecutive series of dates, each based on the same basic formula.
  5. Inserting rows after an existing row does NOT copy formulae from the one above, unlike the behaviour I expected from Numbers.
  6. You can also drag downwards from a single date and get a series of consecutive dates which are numbers and not formulae
  7. If you want a series of dates that are a different number of days apart, you need to create 2 cells with dates in an appropriate relationship, select both of them, and then drag down. Or you can use the formula trick.
  8. You can also select several cells in the same row, and drag them down as a unit
  9. Copy-Paste also works.
  10. The drag down of dates trick is actually better for my purposes than the formula trick, and likewise better for my purposes than the insert-whole-line method from Numbers, since I can choose which cells (not) to copy.

This answer would be complete (for my purposes) if I also included how to convert strings to numbers, so that it would work for mis-imported data from other spreadsheets. But while I’ve seen references to how to do that, I haven’t internalized them yet.

Edited to add:

Including the following by reference:

https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data/en

Note that I have not tried any of this yet. But if it works, and covers everything a naive Numbers user needs, this is now a complete answer.

#21

I downloaded some Numbers file from the internet. It looks like an ordinary spreadsheet with correct dates.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.