# Add missing dates to list of dates with values

Current Data:

``````9/20/2018   6
9/22/2018   3
9/23/2018   1
``````

Desired Output:

``````9/20/2018   6
9/21/2018
9/22/2018   3
9/23/2018   1
``````

I would like to have the missing dates added to Column A while the data in Column B remains matched up with the original date. Column B Does not necessarily need to receive a Null value, it could be '0' as well.

• This output list could be constructed on another sheet via a complete list of dates and e.g. VLOOKUP for the column B values. You could then even replace the original list with the results. You chose the tag google-sheets-custom-function though. Will you have to do this repeatedly? If actually coding a custom function, have you made a start at it? What code do you have so far? What doesn't work? See: How to Ask. – Joel Reid May 6 at 18:45

• paste this into C1 cell:

``````=ARRAYFORMULA({TO_DATE(ROW(
INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), IFERROR(VLOOKUP(TO_DATE(ROW(
INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), A:B, 2, 0))})`````` • then select columns C:D
• press `CTRL` + `C`
• press `DELETE`
• select A1 cell
• press `CTRL` + `SHIFT` + `V`
• This worked perfectly, for anyone else out there: if you are creating that list from another formula you will get an error, use a helper column with "=if(istext(A1),DATEVALUE(A1))" then user0's answer in the column next to it. – Travis Manning May 7 at 22:18

A simpler formula than the one on the other answer.

Add the following formula to `C1`

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

In case that you have a larger source data, Replace A1:B3 and A1:A3 accordingly.

Then do the Copy / Paste as values only steps, either by using the Google Sheets menu options, the contextual menu options (right clic) or the keyboard shortcuts.

Related