how to insert missing dates?

[o1dnik]

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?

[CEAuke]

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

[Cbhihe]

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.

[PYS]

Hi

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…

Regards