Current Data:

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

Desired Output:

9/20/2018   6
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.

  • 1
    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
    Commented May 6, 2019 at 18:45

2 Answers 2

  • paste this into C1 cell:



  • 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. Commented May 7, 2019 at 22:18
  • I had trouble getting this to work when my column "B" (the data) was actually in another column ("H" in my case). Simply replacing the B's with H's didn't work. In the end I just moved my column H to column B. If anyone knows how to generalize "B" to any column, would be much appreciated. Otherwise though this formula worked perfectly, thank you. Commented Nov 27, 2023 at 13:30

A simpler formula than the one on the other answer.

Add the following formula to C1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.