Insert missing dates

**jmiller7** · 01-19-2011, 01:34 PM

I have a set of dates from 1998-2005 but some of the days are missing.Does anyone know how to set up something in excel to find the missing dates and insert rows? Thanks

**alansidman** · 01-19-2011, 01:44 PM

If your dates are sequential and in column A, in a helper column starting with row 2, put in this formula =if (A1+1=A2,"","Missing Date") and copy it down.

Alan

**jmiller7** · 01-21-2011, 12:15 PM

I have a database with daily average transactions from 1998-2005. Some of the days are mssing from the database. I want to create a formula that will find the missing days and insert a row. Does anyone know how to do this. Thanks.My database looks like this

Invoice Date Average Daily Price
1/1 45
1/2 48
1/3 50
1/5 45
1/6 46

I would like to enter arow for 1/4 using a formula.

**Ron Coderre** · 01-21-2011, 12:32 PM

With your posted data in cells A1:B6, with the dates starting in A2...

This regular formula won't insert rows (no formula can do that), but it will list the missing dates:

A7: =SMALL(INDEX(ISNA(MATCH(ROW(INDEX(A:A,MIN($A$2:$A$6)):INDEX
(A:A,MAX($A$2:$A$6))),$A$2:$A$6,0))*ROW(INDEX(A:A,MIN($A$2:$A$6)):
INDEX(A:A,MAX($A$2:$A$6))),0),COUNT($A$2:$A$6)+ROWS($7:7))

Copy that formula down until it returns #NUM! errors.

using your posted data, the formula returns the missing date: 01/04/2011

Is that something you can work with?

**jmiller7** · 01-21-2011, 12:41 PM

Yes this helps. Thank you

**shg** · 01-21-2011, 12:43 PM

Duplicate post, threads merged.

**Mikey_56** · 10-19-2014, 08:51 PM

Hi! is there a formula that can add row in a cell for the missing dates here is my sample :
10/27/2014
10/28/2014
10/29/2014
10/31/2014
11/1/2014
11/2/2014
10/27/2014
10/28/2014
10/29/2014
10/31/2014

I would like to fill in those rows that dont have the dates for the week, hope you can help me on this

**protonLeah** · 10-19-2014, 09:40 PM

Mikey_56,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.