2

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.

Here is an example sheet, feel free to experiment: link

  • 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
0
  • 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))})

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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