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

 

[jasonb75]

I'm sure there must be an easier way that this but I can't find it.

Manexcel.xlsx
	A	B	C	D
2	04/01/2021 09:20	10/01/2021 11:10	04/01/2021 09:20	10/01/2021 14:37
3	04/01/2021 09:51	10/01/2021 14:37
4	11/01/2021 09:21	12/01/2021 15:33	11/01/2021 09:21	14/01/2021 14:37
5	11/01/2021 09:51	14/01/2021 14:30
6	11/01/2021 09:51	14/01/2021 14:37
7	21/01/2021 09:01	24/01/2021 14:37	21/01/2021 09:01	24/01/2021 14:37
8	21/01/2021 09:01	24/01/2021 14:37
9	22/01/2021 09:51	24/01/2021 14:37
10
11			12
Sheet7

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2>B1,A2,"")
D2:D9	D2	=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)))
C11	C11	=SUM(IFERROR(INT(D2:D9-C2:C9),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Arturs]

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]

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.

 

[Arturs]

Yes, I do have the 365 has the =LET() and =FILTER() functions available

Here is the link to full reposrt file - Sample.xlsx

I have tried this function, made it work, but that is unfortunately what I`m after for. - Calculate unique days in multiple overlapping date ranges

 

[jasonb75]

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?

 

[Arturs]

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]

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?

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

 

[Arturs]

Yes, the expected results will be there

 

[jasonb75]

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.