Filling in missing dates

P

picklet222

I have a long series of daily data (> 10 years, one line per day) that is
missing a few hundred dates here and there. Is there any way to get Excel to
insert blank lines for the missing days so that I have a single line for
every day of every year?

Thank you!

HB
 
R

Ron Coderre

Try this:

In a separate worksheet in that workbook:
A1: Date
A2: (enter the earliest date you need)

A3: =A1+1
copy that cell down until the last cell displays the last date you need.

Then
B1: Found
B2: =ISNUMBER(MATCH(A2,Sheet1!$A$1:$A$3651,0))
Copy that formula down as far as you need. It will tell you if the data was
found in the list on Sheet1.

Next,
Data>Filter>Autofilter
Click on the Found cell and set the criteria to FALSE
Select Cell A2, hold down the [Shift] key and press [End] then [Down]
Edit>Copy

Switch to Sheet1
Select the cell that is 1 below the last cell of the Date column.
Edit>Paste Special>Values

Now sort your data list (including the appended dates). The missing dates
will find their way into the data.

(Of course, adjust sheet names and ranges to suit your situation)

Does that help?

***********
Regards,
Ron
 
Top