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

[Arturs]

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]

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

 

[Arturs]

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]

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]

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.
H - Link
I - Reason

Then I`ve added

J - Duratation
K- Adjusted start
L- Adjusted End

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

 

[jasonb75]

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

=ROWS(buffertask3[#All])

into an empty cell. Does that give you the correct number of rows for the buffertask 3 table?

 

[Arturs]

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

 

[jasonb75]

Try

=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]

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]

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]

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.