Ask Your Question

how to insert missing dates?

asked 2014-03-12 15:03:51 +0200

o1dnik gravatar image

I copied a table from another source and want to use it in LibreOffice Calc. But I have some dates missing. I need to insert zero values in missing dates to have a calendar view. How to do that?

edit retag flag offensive close merge delete


How many columns do you have? If you just have dates in sheet1 in Column A and your events in Column B, you can create a new sheet2 first and drag the dates to have a full sequence, then do a lookup to populate the events from sheet1

CEAuke gravatar imageCEAuke ( 2014-05-19 14:02:47 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2015-06-27 13:21:15 +0200

CBhihe gravatar image

Let's say your imported dates are in sheet 1.

On sheet2, across a cell range equivalent to that of sheet1, apply the function:

    IF(Test,Then_value, Otherwise_value)

Test is whether the corresponding cell in sheet1 is empty or not. For that you can use the function:

   CELL(TYPE, Reference of cell in sheet1)

If the cell in sheet1 is blank, CELL() returns "b". If so the Then_value should be "0" or whatever you want. If not so the Otherwise_value should be that of the corresponding cell in sheet1.

edit flag offensive delete link more

answered 2015-06-27 14:01:49 +0200

pierre-yves samyn gravatar image


You can do this with just Copy & Paste:

  1. Copy the all range, keep it selected,
  2. Type 0 and validate with Alt + Enter (the entire range contains zeros),
  3. Edit Paste special: check Paste All & Skip empty cells
  4. Confirm Yes the warning dialog (You are pasting data into cells that already contain data...)

That's it...


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-03-12 15:03:51 +0200

Seen: 1,010 times

Last updated: Jun 27 '15