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]

No, I don`t think part days is a requirement, it is just something I had in the calculation.

 

[jasonb75]

This took a bit of figuring out. First of all some formulas to add to the source table.

Sample.xlsx
	A	B	C	D	E	F
1	Start Date	End Date	Duratation	Item Number	Adjusted start	Adjusted end
2	01/06/2021 10:21	01/06/2021 11:42	0	1	01/06/2021	01/06/2021
3	01/06/2021 10:16		0	2	01/06/2021	01/06/2021
4	01/06/2021 10:16		0	3	01/06/2021	01/06/2021
5	01/06/2021 10:08		0	4	01/06/2021	01/06/2021
6	01/06/2021 08:55		0	5	01/06/2021	01/06/2021
7	31/05/2021 12:57		1	5	31/05/2021	01/06/2021
8	31/05/2021 08:49	01/06/2021 10:12	1	6	31/05/2021	01/06/2021
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=INT([@[Start Date]])
F2:F8	F2	=INT(IF([@[End Date]]="",NOW(),[@[End Date]])-MOD([@[Start Date]],1))
C2:C8	C2	=INT(IF([@[End Date]]="",NOW(),[@[End Date]])-[@[Start Date]])

Then the results table. Note that I've also used formulas to list the item numbers in column A and the dates in row 1, this method omits any items / months with no data from the results.
There are some fairly simple ways to blank out the zero result combinations if required.

Sample.xlsx
	A	B	C	D	E	F	G	H
1	Item Nr.	01/12/2020	01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021
2	1	0	0	0	0	4	5	0
3	2	0	0	0	0	2	0	0
4	4	0	0	0	0	0	8	1
5	5	0	0	0	0	0	9	1
6	6	0	0	0	2	0	1	1
7	10	0	0	0	0	4	18	1
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	=LET(s,EOMONTH(MIN(buffertasks3[Start Date]),-1)+1,e,EOMONTH(TODAY(),-1)+1,EDATE(s,SEQUENCE(,DATEDIF(s,e,"M")+1,0)))
A2:A127	A2	=SORT(UNIQUE(FILTER(buffertasks3[Item Number],buffertasks3[Duratation]>0)))
B2:H7	B2	=LET(a,UNIQUE(FILTER(buffertasks3[[Adjusted start]:[Adjusted end]],(buffertasks3[[Duratation]:[Duratation]]>0)*(buffertasks3[[Item Number]:[Item Number]]=$A2))),s,FILTER(a,{1,0}),e,FILTER(a,{0,1}),SUM((MMULT(TRANSPOSE(s^0),(TRANSPOSE(MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+ROW(B$1:INDEX($B:$B,MEDIAN(MAX(e),B$1,EOMONTH(B$1,0))-MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+1))-1)>=s)*((TRANSPOSE(MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+ROW(B$1:INDEX($B:$B,MEDIAN(MAX(e),B$1,EOMONTH(B$1,0))-MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+1))-1))<=e))>0)*1))
Dynamic array formulas.

 

[Arturs]

Hey @jasonb75
Thank you for your help.

Sample 1 - Sample (1).xlsx

I`ve added a link to the file where I put the formulas in. Am I doing something wrong?

Is it possible for you to share the file you created?

But your work just looks amazing - it is a different level to mine

 

[jasonb75]

Is it possible for you to share the file you created?

It is possible but forum rules require that I post the solution in the thread rather than attaching a file. It is for this reason that I also said that an XL2BB sample is preferable to an attached file.

From what I can see, the formulas in A2 and B1 have been entered as legacy arrays instead of dynamic.

Delete all of the formulas from column A, then enter the formula into A2 only. Enter it as a normal formula, do not use Ctrl Shift Enter.
Delete all of the formulas from row 1 and enter the formula into B1 only. As before, enter as a normal formula, do not use Ctrl Shift Enter.

For the formula in the main table, enter it into B2 only, again as a normal formula. Then use the fill handle to drag it right and down to fill the rest of the table.

It may be a formula translation error, but I noticed that {1,0} and {0,1} had changed to {1;0} and {0;1} resulting in #VALUE! errors in the result table.
If your system settings use ; instead of \ then it could be possible that you had changed those to \ instead of ;

 

[Arturs]

Unfortunately I cannot install on work PC any add-ins, so I will try installing XL2BB at home.

Yes, you are correct, as I have formula separators ";" instead of '' , " I did use the replace f-ction to adjust the formula, and also have removed necessary formula " , " too.

As I know the issue, I will try to change the settings, if I can and make it work.

 

[jasonb75]

Unfortunately I cannot install on work PC any add-ins, so I will try installing XL2BB at home.

That's not so critical at the moment, I have the example in the file that you linked earlier and have already posted it in XL2BB format in the thread. It's simply preferred as a lot of people can't download linked files.

I can't remember my login credentials to upload the file. Hopefully you will be able to get it working from the formulas but if not I'll try and get the file uploaded later.

 

[Arturs]

This is magnificent- I actually made it work.

I have a problem with March tho.
I do excactly the same things with cell E2 like the rest, but I have an error

I have a question - In July, when new data will start coming in, in Sheet 1 1st row will automatically add new month? And the Item Nr. column will update the unique Items as well?

 

[jasonb75]

I do excactly the same things with cell E2 like the rest, but I have an error

I had the same when I copied the mini sheet from my earlier post, although the formula appears to be correct it errors for some reason.
If you select from B2 to the last cell in the table (H127 with the sample file), then press F2 followed by Ctrl Enter (no shift). That should enter the formulas correctly.

I have a question - In July, when new data will start coming in, in Sheet 1 1st row will automatically add new month? And the Item Nr. column will update the unique Items as well?

If you have them set up dynamically (as described in my previous reply) then yes, they will adjust automatically to reflect any additions / changes in the Buffertasks3 table.
Unfortunately it is not possible to do the same thing with the formula that goes into B2, this one still needs to be filled to the table manually.

I have just noticed that the formula is over counting, it counts number of days rather than duration so I will need to look at that and make some adjustments to it. I will try and get this done later today but may possibly not get chance to post an updated formula until tomorrow morning (UK time).

 

[Arturs]

Thank you for noticing the overcounting - I didn`t even notice that.

I have set up dynamically the months row and I will copy the formula to the next column as described - Ctrl Enter. I did fix the March column as well.

Whenever you have a chance - You are helping me a lot with this solution

 

[jasonb75]

I've checked over a few random results and this appears to work.

I've shown the formula from my earlier reply below with a small section, >=s highlighted (drag the scrollbar right to see it). To correct the figures, you simply need to remove the = sign from that part, changing it to >s Then reapply it to the whole table.

=LET(a,UNIQUE(FILTER(buffertasks3[[Adjusted start]:[Adjusted end]],(buffertasks3[[Duratation]:[Duratation]]>0)*(buffertasks3[[Item Number]:[Item Number]]=$A2))),s,FILTER(a,{1,0}),e,FILTER(a,{0,1}),SUM((MMULT(TRANSPOSE(s^0),(TRANSPOSE(MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+ROW(B$1:INDEX($B:$B,MEDIAN(MAX(e),B$1,EOMONTH(B$1,0))-MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+1))-1)>=s)*((TRANSPOSE(MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+ROW(B$1:INDEX($B:$B,MEDIAN(MAX(e),B$1,EOMONTH(B$1,0))-MEDIAN(MIN(s),B$1,EOMONTH(B$1,0))+1))-1))<=e))>0)*1))

I would suggest doing a check of the results for a few items, mainly looking at rows where the start and end dates are in different months, or those where there are 2 or more distinct periods in a single month (i.e. date ranges that do nor overlap each other).