# 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

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

##### New Member

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

##### Well-known Member
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

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
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
Yes, the expected results will be there

#### jasonb75

##### Well-known Member
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.

Replies
6
Views
39
Replies
4
Views
142
Replies
6
Views
397
Replies
4
Views
322
Replies
1
Views
70

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.

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