# Fill zeros for the dates when no data was recorded

I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for these missing dates. How to achieve this, preferably with a formula that does not need to be copied down the sheet?

``````+---+-----------+-------+
|   |     A     |   B   |
+---+-----------+-------+
| 1 | Date      | Value |
| 2 | 3/25/2016 | 25    |
| 3 | 3/26/2016 | 15    |
| 4 | 3/28/2016 | 74    |
+---+-----------+-------+
``````

Desired output:

``````+---+-----------+-------+
|   |     C     |   D   |
+---+-----------+-------+
| 1 | Date      | Value |
| 2 | 3/25/2016 | 25    |
| 3 | 3/26/2016 | 15    |
| 4 | 3/27/2016 | 0     |
| 5 | 3/28/2016 | 74    |
+---+-----------+-------+
``````

I used two arrayformulas for this purpose. One, in cell C2, to create the list of dates:

``````=arrayformula(if(row(C2:C)+A2-2 > max(A2:A), , row(C2:C)+A2-2))
``````

It adds the row number minus 2 to the date in A2. If the result is greater than the last date in column A, this date is out of range and is not shown.

Then in column D, `vlookup` is used to locate the value for each date. If it cannot find the exact date (raising an error), 0 is used.

``````=arrayformula(iferror(vlookup(filter(C2:C, len(C2:C)), filter(A2:B, len(A2:A)), 2, false), 0))
``````

The last parameter of `vlookup`, "sorted" is set to False in order to force exact match, even though the array of dates is actually sorted.

• With some local language settings you have to use colons `;` instead of commas. – Guglie Jan 5 '18 at 9:25

A simpler solution (single formula) than the one on the other answer.

Add the following formula to `C2`

``````=ArrayFormula({
A1:B4;
FILTER({A1+ROW(A1:A4),IFERROR(ROW(A1:A4)/0,0)},
ISNA(MATCH(A1+ROW(A1:A4),A:A,0)))
})
``````

NOTE:

• This is similar but not exactly the same content from my answer to the related questions. On the other answers, instead of 0 a blank is added on inserted dates.

Related