# Count Unique Days Of an Item Between 2 dates which is larger than 1

#### Arturs

##### New Member
Hello,

I have been trying to figure out a formula to see which materials are missing in the production for more than 1 day.

I have a 3 column report which I get sent every month - Start date, End date and Item Nr. (sheet1, columns A, B and C). I have formatted to short date columns A and B, but they do have time included as well.

In sheet2 I have created a Table :

Column A "Item Numbers" where I have removed all duplicates for Item Nr. from sheet 1
Column B - Date beginning 2021.01.01

I have been asked to try to create a report, where I can see how many unique days, an Item has been missing every month.
A missing item in production does not count as missing, if it is missing for one day (less than 24hrs).

In my previous attempt I created a column which had a number of days missing, but I could only manage to sum up the total amount of days per month. Could not find the formula to calculate unique days.

Ideally, in sheet2 cell B2 I would like to have 12 days.

Hope you can help, as I`m stuck for a couple days now

#### Arturs

##### New Member
I removed the "=" sign and all worked well. I`ve checked Item numbers 20,36 and 141 and everything is working perfectly.

Thank you, Jason, for helping me achieve the result

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Arturs

##### New Member
When providing you with data, I removed original Item numbers drom the excel list.

Now, when I`m trying to create a list of non-duplicate Item numbers I get a list of 9 Items. Can you direct me to a way how to solve this issue? As the list should contain the exact amount of items numbers we had in the sample file.

#### jasonb75

##### Well-known Member
Did you remove rows, or columns?

If you removed rows then the only way for it to fail would be if some of the data was outside of the table.

If you removed columns then the formula might need some adjustment. I would need to know how the columns have changed in order to advise on correcting the formula for this.

#### Arturs

##### New Member
Hi Jason,

The original report contains 9 Columns (buffertasks table)

A - Start date
B - End date
C - Item Nr.
D - System Klient
E - Client
F - Project
G - Production Nr.
I - Reason

J - Duratation

Sheet 1 - I have not changed. Formulas in A1, B1 and B2 have stayed the same.

#### jasonb75

##### Well-known Member
Based on that, there is no reason for the formula to fail. I though that it was unlikely to be a column issue anyway as it was giving you some results. Unless you have changed the formula that lists the item numbers then the problem will be with the data / table, not with the formula.

Try entering
Excel Formula:
``=ROWS(buffertask3[#All])``
into an empty cell. Does that give you the correct number of rows for the buffertask 3 table?

#### Arturs

##### New Member
Yes, all is correct 836 in the sample file, 854 - in the renewed table

#### jasonb75

##### Well-known Member
Try
Excel Formula:
``=UNIQUE(buffertasks3[Item Number])``
does that still give you 9 items, or the whole list?

** Use an empty column to test this, if there is anything in the cells below the formula then you will likely get a #CALC! error **

#### Arturs

##### New Member
The above formula gives a list of all Unique Items and the #CALC error is in every cell after row 22 (B-H) columns.

#### jasonb75

##### Well-known Member
The calc error in those columns will be caused by item numbers that have no records with a duration greater then zero in the table.

Check the duration and adjusted start / end columns for an item that you think should be returning a result but isn't.

#### Arturs

##### New Member
Yes, It gives the exact result, with #CALC error, when column contains unnecessary (less than 1 day) Item Nr.

But this as well seems to work only to row 22.

Replies
1
Views
71
Replies
10
Views
163
Replies
11
Views
347
Replies
3
Views
98
Replies
0
Views
252

1,186,112
Messages
5,955,901
Members
438,225
Latest member
rsur

### 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