# Find missing dates

#### jackt05

##### Board Regular
Hi,

I have two columns as follows

Dates Cab Nos.

1-Jan 4501
2-Jan 4501
3-Jan 4501
5-Jan 4501
1-Jan 8409
2-Jan 8409
5-Jan 8409

there are more than 4000 entries with different cab nos for the whole month. Here 4501 cab is absent on the 4th of Jan and cab 8409 is absent on the 3rd and 4th Jan. I need to find out the dates on which the particular cab is absent I have about fifty different cabs.

Thank You

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Assumptions:

Code:
``````A2:B8 contains the data

D3 contains the first day of January  ----->  1/1/2009

E3 contains the last day of January  ----->   1/31/2009

F1 contains 4501

G1 contains 8409``````

Formulas:

Code:
``````F3, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=SUM(IF(ISNA(MATCH(ROW(INDIRECT(\$D\$3&":"&\$E\$3)),IF(\$B\$2:\$B\$8=F\$1,\$A\$2:\$A\$8),0)),1))``````

Code:
``````F4, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(F\$4:F4)<=F\$3,SMALL(IF(ISNA(MATCH(ROW(INDIRECT(\$D\$3&":"&\$E\$3)),IF(\$B\$2:\$B\$8=F\$1,\$A\$2:\$A\$8),0)),ROW(INDIRECT(\$D\$3&":"&\$E\$3))),ROWS(F\$4:F4)),"")``````

Hope this helps!

Thanks Domenic,

I copied our formula, what should the result be, its coming 1 & some where 0

which date is missing please if you wish give in detail

Thanks once again

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula. Does this help?

Replies
4
Views
272
Replies
2
Views
356
Replies
8
Views
364
Replies
0
Views
397
Replies
0
Views
306

1,202,962
Messages
6,052,805
Members
444,602
Latest member
Cookaa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back