Is there a way to make excel expand dates in a spreadsheet

Question

I have a large two column data set that already has dates and data, whoever I need the dates to "expand" for daily data

I already attempted to auto-fill however it doesn't do what I need it to

I have a spreadsheet like this

date        Data
07/01/19     5
07/03/19     10
09/05/19     7

I'd like to expand the dataset like

date        Data
07/01/19     5
07/02/19     
09/03/19     10
07/04/19     
07/05/19     7

`09/03/19` does not make much sence in your expected output to me. Can you also include what else you have tried other than autofill. Are you working with VBA or worksheet functions? — JvdV, Sep 04 '19 at 14:44
It looks like you have two typos (`9/5` & `9/3`). Assuming these are all meant to be July, it looks like you want to expand on your range to not skip any dates and have the associated data as `blank` for these dates. If so, you will either need to create another sheet and use `VLOOKUPS` to import values or use VBA ( one way is to sort and loop through range inserting rows where blanks appear) — urdearboy, Sep 04 '19 at 14:48
The VLOOKUPS helped... I usually use R but the loaner computer I have today doesn't have it installed.. I'm like a fish out of water — Island868, Sep 04 '19 at 15:04
Assuming like @urdearboy that your inconsistent data is a typo, one way to do it is to add the full range or dates that covers your data at the bottom of your date column, then remove duplicates and sort from low to high. — Plutian, Sep 04 '19 at 15:05
Apparently you can use [`DAX`](https://stackoverflow.com/questions/52936545/add-missing-date-rows-in-power-bi-power-query-and-take-value-of-row-above) for this. — JvdV, Sep 04 '19 at 16:03

score 0 · Answer 1 · answered Sep 04 '19 at 15:09

0

Create another sheet with all of the dates that you need in column A. Then use this formula to look up your Data (column B) against these dates:

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),"")

It will match the desired output you have posted above.

answered Sep 04 '19 at 15:09

GodspeedYou77

score 0 · Answer 2 · answered Sep 05 '19 at 10:51

With your data in columns A:B, you can use the following formulas:

D2: =IFERROR(INDEX(ROW(INDEX($A:$A,MIN($A:$A),1):INDEX($A:$A,MAX($A:$A),1)),ROWS($1:1)),"")
E2:  =IFERROR(VLOOKUP(D2,$A:$B,2,FALSE),"")

and fill down

note that this formula will fail after 25 Nov 4770, but you'll probably be using a different tool by then :-)

2 Answers2