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

Arturs

New Member
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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.

Sheet11.PNG


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

Sheet2.PNG


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.

prev.att.png


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
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Arturs

New Member
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Yes, all is correct 836 in the sample file, 854 - in the renewed table
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,816
Messages
5,708,746
Members
421,588
Latest member
Wawie

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top