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 :(
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
I'm sure there must be an easier way that this but I can't find it.
Manexcel.xlsx
ABCD
204/01/2021 09:2010/01/2021 11:1004/01/2021 09:2010/01/2021 14:37
304/01/2021 09:5110/01/2021 14:37  
411/01/2021 09:2112/01/2021 15:3311/01/2021 09:2114/01/2021 14:37
511/01/2021 09:5114/01/2021 14:30  
611/01/2021 09:5114/01/2021 14:37  
721/01/2021 09:0124/01/2021 14:3721/01/2021 09:0124/01/2021 14:37
821/01/2021 09:0124/01/2021 14:37  
922/01/2021 09:5124/01/2021 14:37  
10
1112
Sheet7
Cell Formulas
RangeFormula
C2:C9C2=IF(A2>B1,A2,"")
D2:D9D2=IF(C2="","",MAXIFS($B$2:$B$9,$A$2:$A$9,"<"&IFERROR(1/(1/MINIFS($C3:$C$10,$C3:$C$10,">"&C2)),MAX($A$2:$A$9)+1)))
C11C11=SUM(IFERROR(INT(D2:D9-C2:C9),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Arturs

New Member
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hey jasonb75, thank you for your help.
This would work if I had date values for one item in cells A and B. But in the original report (the first picture) I have close to 900 rows with 250 unique item numbers.

And I think I need Sum not Count... like I mentioned in the subject.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
It is sum, not count (SUM in the C11 formula is a bit of a clue there). Regardless of that, I had based it off of the last screen capture, with the first one it is going to take a lot more to make it work.

It might be possible using a variation of the method shown here. I haven't looked at it in enough detail to see if it would work with the added criteria of the item numbers.

I would want a reasonable sized usable sample to test it on before even attempting to make that work (that means approximately 50 rows posted with (preferrred) XL2BB or an actual workbook uploaded to a file share location, not a screen capture or a basic copy and paste).

Also, please confirm if your version of office 365 has the =LET() and =FILTER() functions, many people are still missing one or both of these at the present time.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
What should be done to the rows with no end date?

Also, take a look at row 190 which overlaps 2 months. How should that be posted in the results table?
 
Last edited:

Arturs

New Member
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The empty cells should be counted as today(), if that is possible, and the purpose of the report is to see how many unique days per month the product is missing in production. And I think row 190 should be counted up until the end of the month.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
The empty cells should be counted as today(), if that is possible,
So how about changing the formula in the duration column to this one?
Excel Formula:
=INT(IF([@[End Date]]="",NOW(),[@[End Date]])-[@[Start Date]])
Does that yield the expected results there?

edit:-

I just noticed that your original duration formula includes part days rather than rounding down to the nearest whole day. If part days are a requirement then this just took a giant leap towards being impossible.
 
Last edited:

Arturs

New Member
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Yes, the expected results will be there
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
Did you see the edit to my last reply? I think that it may have crossed with yours.
I just noticed that your original duration formula includes part days rather than rounding down to the nearest whole day. If part days are a requirement then this just took a giant leap towards being impossible.
 

Forum statistics

Threads
1,141,816
Messages
5,708,753
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