Date Ranges

[Scarpacci]

In Excel 2002 I have a column that has Unique Account #s and an other column that lists their invoice date.

Example:
Account# Invoice_Date
12345 7/11/2003
12345 8/11/2003
12345 11/11/2003
12345 12/11/2003

Is there a away that I can find the Account # and the Invoices that are missing with the Criteria listed above? So I wanted to be able to find Accounts with missing invoices and what invoices are missing by invoice date.

 

[Nimrod]

What criteria are you refering to ?
What indicates , to you, that an invoice is missing ?

 

[Scarpacci]

See how in my example I had 8/11/2003 and then it jumped to 11/11/2003. I want to find a way to identify that the 9/11/2003 and 10/11/2003 invoices are missing automatically.

Thanks for responding!

S

 

[Aladin Akyurek]

See how in my example I had 8/11/2003 and then it jumped to 11/11/2003. I want to find a way to identify that the 9/11/2003 and 10/11/2003 invoices are missing automatically.

Thanks for responding!

S

How do the missing dates that are in fact non-workday or holiday dates fit the picture?

 

[Scarpacci]

They don't matter. The invoices are automatically generated same date each month. For different accounts the dates can be different, but for each account its bill day remains the same, the date just changes. Make sense?

thanks

S

 

[Aladin Akyurek]

They don't matter. The invoices are automatically generated same date each month. For different accounts the dates can be different, but for each account its bill day remains the same, the date just changes. Make sense?

thanks

S

What follows is rather elaborate, but it does the job...

The scheme requires that the data is sorted on Account#.

Data

Book12
	A	B	C	D
1	Account#	Invoice_Date
2	12345	11-Jul-03
3	12345	11-Aug-03
4	12345	11-Nov-03
5	12345	11-Dec-03
6	12367	13-Jul-03
7	12367	13-Aug-03
8	13001	24-Jul-03
9	13001	24-Aug-03
Data

Audit

Book12
	A	B	C	D	E	F
1	Audit			FullI_DateList	0	MissingDates
2	Account#	12345		11-Jul-03		11-Sep-03
3	Start	1		11-Aug-03		11-Oct-03
4	End	4		11-Sep-03	1
5	Min	11-Jul-03		11-Oct-03	2
6	Max	11-Dec-03		11-Nov-03
7	N	6		11-Dec-03
8	Missing	2
9
10
11
12
Audit

Formulas...

B3:

=MATCH(B2,Data!A2:A9,0)

B4:

=MATCH(B2,Data!A2:A9)

B5:

=MIN(INDEX(Data!B2:B9,B3):INDEX(Data!B2:B9,B4))

B6:

=MAX(INDEX(Data!B2:B9,B3):INDEX(Data!B2:B9,B4))

B7:

=DATEDIF(B5,B6,"m")+1

B8:

=B7-COUNT(INDEX(Data!B2:B9,B3):INDEX(Data!B2:B9,B4))

D2:

=IF(COUNT($D$1:D1)<$B$7,EDATE($B$5,COUNT($D$1:D1)),"")

E1 must house a 0.

E2:

=IF(N(D2)*ISNA(MATCH(D2,INDEX(Data!$B$2:$B$9,$B$3):INDEX(Data!$B$2:$B$9,$B$4),0)),LOOKUP(9.99999999999999E+307,$E$1:E1)+1,"")

F2:

=IF(ROW()-ROW($F$2)+1<=$B$8,INDEX($D$2:$D$12,MATCH(ROW()-ROW($F$2)+1,$E$2:$E$12,0)),"")

 

[Nimrod]

When you say a column has unique Acc numbers does that the account number in that one column is the same thu out the range? So you have a seperate column for each account Number ? If so what columns are you using ?