Add missing dates to list of dates with values

Question

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.

Answer 1

• 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

Answer 2

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

• Fill zeros for the dates when no data was recorded
• Adding missing dates to Google Sheets
  My answer to this question includes a long explanation, including how to use this formula as data source for QUERY.