Retrieving days per month that tanks are empty using filling and emptying dates

[AllardZ]

Hi everyone. First post here, so please let me know if anything is not according to the guidelines.

This is a snippet from a table in which data is collected from the brewery I work at (not all columns are depicted):

batch #	tank number	brew date	empty date
22.271	17	15-08-22	30-08-22
22.272	17	15-08-22	30-08-22
22.273	18	16-08-22	08-09-22
22.274	18	16-08-22	08-09-22
22.275	13	17-08-22	02-09-22
22.276	13	17-08-22	02-09-22
22.277	4	18-08-22	01-09-22
22.278	1	19-08-22	19-09-22
22.279	5	22-08-22	06-09-22
22.280	6	22-08-22	06-09-22
22.281	16	23-08-22	09-09-22
22.282	16	23-08-22	09-09-22
22.283	15	24-08-22	07-09-22
22.284	15	24-08-22	07-09-22
22.285	14	25-08-22	12-09-22
22.286	14	25-08-22	12-09-22
22.287	11	29-08-22	13-09-22
22.288	11	29-08-22	13-09-22
22.289	12	30-08-22	16-09-22
22.290	12	30-08-22	16-09-22
22.291	17	31-08-22	14-09-22
22.292	17	31-08-22	14-09-22
22.293	7	01-09-22	15-09-22
22.294	8	05-09-22	20-09-22
22.295	4	05-09-22	19-09-22
22.296	9	06-09-22	20-09-22
22.297	13	06-09-22	21-09-22
22.298	13	06-09-22	21-09-22
22.299	2	07-09-22	22-09-22
22.300	15	08-09-22	23-09-22
22.301	15	08-09-22	23-09-22
22.302	6	09-09-22	22-09-22
22.303	16	12-09-22	26-09-22
22.304	16	12-09-22	26-09-22
22.305	18	13-09-22	29-09-22
22.306	18	13-09-22	29-09-22
22.307	3	14-09-22	27-09-22
22.308	5	14-09-22	30-09-22
22.309	11	15-09-22	03-10-22
22.310	11	15-09-22	03-10-22
22.311	7	19-09-22	07-10-22
22.312	12	19-09-22	04-10-22
22.313	12	20-09-22	04-10-22
22.314	14	20-09-22	05-10-22
22.315	14	21-09-22	05-10-22
22.316	8	21-09-22	10-10-22
22.317	1	22-09-22	06-10-22
22.318	4	22-09-22	06-10-22
22.319	17	26-09-22	12-10-22
22.320	17	26-09-22	12-10-22
22.321	13	27-09-22	13-10-22
22.322	13	27-09-22	13-10-22
22.323	15	29-09-22	14-10-22
22.324	15	29-09-22	14-10-22
22.325	2	30-09-22	17-10-22
22.326	6	30-09-22	17-10-22
22.327	3	03-10-22	19-10-22
22.328	9	04-10-22	19-10-22
22.329	11	05-10-22	21-10-22
22.330	12	06-10-22	20-10-22
22.331	12	06-10-22	20-10-22
22.332	4	10-10-22	24-10-22
22.333	7	10-10-22	27-10-22
22.334	14	11-10-22	25-10-22
22.335	14	11-10-22	25-10-22
22.336	16	12-10-22	26-10-22
22.337	16	12-10-22	26-10-22
22.338	17	13-10-22	27-10-22
22.339	17	13-10-22	27-10-22
22.340	8	14-10-22	28-10-22
22.341	5	14-10-22	28-10-22
22.342	1	17-10-22	31-10-22
22.343	13	17-10-22	01-11-22
22.344	13	17-10-22	01-11-22
22.345	6	18-10-22	31-10-22
22.346	15	19-10-22	02-11-22
22.347	15	19-10-22	02-11-22
22.348	18	20-10-22	03-11-22
22.349	18	20-10-22	03-11-22
22.350	2	21-10-22	07-11-22
22.351	3	21-10-22	07-11-22
22.352	11	24-10-22	08-11-22
22.353	11	24-10-22	8-11-22
22.354	12	25-10-22	9-11-22
22.355	12	25-10-22	9-11-22
22.356	14	26-10-22	15-11-22
22.357	14	26-10-22	15-11-22
22.358	16	27-10-22	14-11-22
22.359	16	27-10-22	14-11-22
22.360	9	28-10-22	10-11-22
22.361	4	28-10-22	16-11-22
22.362	17	31-10-22	18-11-22
22.363	17	31-10-22	18-11-22
22.364	7	1-11-22	16-11-22
22.365	8	1-11-22	17-11-22
22.366	13	2-11-22	17-11-22
22.367	13	2-11-22	17-11-22
22.368	15	3-11-22	7-12-22
22.369	15	3-11-22	7-12-22
22.370	18	7-11-22	22-11-22
22.371	18	7-11-22	22-11-22
22.372	1	8-11-22	21-11-22
22.373	2	8-11-22	21-11-22
22.374	11	9-11-22	23-11-22
22.375	11	9-11-22	23-11-22
22.376	12	10-11-22	25-11-22
22.377	12	10-11-22	25-11-22
22.378	3	11-11-22	24-11-22
22.379	5	11-11-22	29-11-22
22.380	6	14-11-22	28-11-22
22.381	9	14-11-22	28-11-22
22.382	16	15-11-22	1-12-22
22.383	16	15-11-22	1-12-22

It has columns for batch number, tank number, brew date and tank emptying date. Now, I need to extract the number of days, by month, that a tank is unused, i.e. empty. For example, tank 5 was emptied on the 6th of september, but subsequently filled on the 14th, so it was empty for the 7 days in between. To make things even more complicated (at least to me): if a tank was emptied on a Friday, but filled on a Monday, the weekend shouldn't be considered as unused days.

I've been thinking of a way to subtract the filling date of a tank from the previous emptying date of said tank, but that does not give me dates. I'm thinking it might not be possible with just a pivot table, maybe a VBA code that loops through all the days, per tank, and registers the ones that don't fall in between filling and emptying dates? What would that look like?

Any ideas? I did find out it seems to resemble some questions related to employee absence days calculations, but I haven't been able to find a suitable solutions.

Thank you in advance,
Allard

 

[Skybluekid]

Hi

What version of Excel are you using?

KR

 

[Skybluekid]

I have created a table for all the tank numbers and Inserted formulas to get the information

I have noticed that there are duplications of empty dates with the same tank number, is this relevant? If not, then they below might help.

In H3 the formula to bring back the last Brew Date is

In I3 the formula to bring back the last but one Empty Date is

To work out the the difference in days is in J3, excluding Weekends

There are other formulas that you can use, depending on the version of Excel.

Hope this helps

 

[AllardZ]

I have created a table for all the tank numbers and Inserted formulas to get the information

View attachment 81792

I have noticed that there are duplications of empty dates with the same tank number, is this relevant? If not, then they below might help.

In H3 the formula to bring back the last Brew Date is
View attachment 81793
In I3 the formula to bring back the last but one Empty Date is
View attachment 81794
To work out the the difference in days is in J3, excluding Weekends
View attachment 81795
There are other formulas that you can use, depending on the version of Excel.

Hope this helps

Hi,

Thank you very much for your quick answer and effort. Much appreciated. I haven't used AGGREGATE at all yet, so figuring out what you've done here taught me some new tricks.

Could you please explain the reason for subtracting ROW($D$2)?

We use Excel 365, version 2211.

I haven't been able to make these formulas work for me completely yet, but it also isn't the exact format I'm looking for. The optimal format would be a (pivot) table that has months/years as columns, tank numbers as rows and 'days empty' as values. In other words, specified per month. Do you think this is possible?

Duplications of empty dates is because of multiple brews being fermented in the same tank, but this could be filtered by the column that specifies whether it's the first or second brew per tank.

 

[Skybluekid]

In the aggregate function, you need to indicate the number of Rows within the Array, not the row numbers on the excel sheet. So the first part will give you row numbers 3-107. What you need is the numbers 1-105, so in order to get that you subtract the Row D2.

As you are using 365, you can use other functions.

Try the below:

Brew Dates.xlsx
	B	C	D	E		G
2	batch #	tank number	brew date	empty date		Days
3	22.271	17	15/08/2022	30/08/2022		-12
4	22.272	17	15/08/2022	30/08/2022
5	22.273	18	16/08/2022	08/09/2022
6	22.274	18	16/08/2022	08/09/2022
7	22.275	13	17/08/2022	02/09/2022
8	22.276	13	17/08/2022	02/09/2022
9	22.277	4	18/08/2022	01/09/2022
10	22.278	1	19/08/2022	19/09/2022
11	22.279	5	22/08/2022	06/09/2022
12	22.28	6	22/08/2022	06/09/2022
13	22.281	16	23/08/2022	09/09/2022
14	22.282	16	23/08/2022	09/09/2022
15	22.283	15	24/08/2022	07/09/2022
16	22.284	15	24/08/2022	07/09/2022
17	22.285	14	25/08/2022	12/09/2022
18	22.286	14	25/08/2022	12/09/2022
19	22.287	11	29/08/2022	13/09/2022
20	22.288	11	29/08/2022	13/09/2022
21	22.289	12	30/08/2022	16/09/2022
22	22.29	12	30/08/2022	16/09/2022
23	22.291	17	31/08/2022	14/09/2022		2
24	22.292	17	31/08/2022	14/09/2022
25	22.293	7	01/09/2022	15/09/2022
26	22.294	8	05/09/2022	20/09/2022
27	22.295	4	05/09/2022	19/09/2022		3
28	22.296	9	06/09/2022	20/09/2022
29	22.297	13	06/09/2022	21/09/2022		3
30	22.298	13	06/09/2022	21/09/2022
31	22.299	2	07/09/2022	22/09/2022
32	22.3	15	08/09/2022	23/09/2022		2
33	22.301	15	08/09/2022	23/09/2022
34	22.302	6	09/09/2022	22/09/2022		4
35	22.303	16	12/09/2022	26/09/2022		2
36	22.304	16	12/09/2022	26/09/2022
37	22.305	18	13/09/2022	29/09/2022		4
38	22.306	18	13/09/2022	29/09/2022
39	22.307	3	14/09/2022	27/09/2022
40	22.308	5	14/09/2022	30/09/2022		7
41	22.309	11	15/09/2022	03/10/2022		3
42	22.31	11	15/09/2022	03/10/2022
43	22.311	7	19/09/2022	07/10/2022		3
44	22.312	12	19/09/2022	04/10/2022		2
45	22.313	12	20/09/2022	04/10/2022		-11
46	22.314	14	20/09/2022	05/10/2022		7
47	22.315	14	21/09/2022	05/10/2022		-11
48	22.316	8	21/09/2022	10/10/2022		2
49	22.317	1	22/09/2022	06/10/2022		4
50	22.318	4	22/09/2022	06/10/2022		4
51	22.319	17	26/09/2022	12/10/2022		9
52	22.32	17	26/09/2022	12/10/2022
53	22.321	13	27/09/2022	13/10/2022		5
54	22.322	13	27/09/2022	13/10/2022
55	22.323	15	29/09/2022	14/10/2022		5
56	22.324	15	29/09/2022	14/10/2022
57	22.325	2	30/09/2022	17/10/2022		7
58	22.326	6	30/09/2022	17/10/2022		7
59	22.327	3	03/10/2022	19/10/2022		5
60	22.328	9	04/10/2022	19/10/2022		11
61	22.329	11	05/10/2022	21/10/2022		3
62	22.33	12	06/10/2022	20/10/2022		3
63	22.331	12	06/10/2022	20/10/2022
64	22.332	4	10/10/2022	24/10/2022		3
65	22.333	7	10/10/2022	27/10/2022		2
66	22.334	14	11/10/2022	25/10/2022		5
67	22.335	14	11/10/2022	25/10/2022
68	22.336	16	12/10/2022	26/10/2022		13
69	22.337	16	12/10/2022	26/10/2022
70	22.338	17	13/10/2022	27/10/2022		2
71	22.339	17	13/10/2022	27/10/2022
72	22.34	8	14/10/2022	28/10/2022		5
73	22.341	5	14/10/2022	28/10/2022		11
74	22.342	1	17/10/2022	31/10/2022		8
75	22.343	13	17/10/2022	01/11/2022		3
76	22.344	13	17/10/2022	01/11/2022
77	22.345	6	18/10/2022	31/10/2022		2
78	22.346	15	19/10/2022	02/11/2022		4
79	22.347	15	19/10/2022	02/11/2022
80	22.348	18	20/10/2022	03/11/2022		16
81	22.349	18	20/10/2022	03/11/2022
82	22.35	2	21/10/2022	07/11/2022		5
83	22.351	3	21/10/2022	07/11/2022		3
84	22.352	11	24/10/2022	08/11/2022		2
85	22.353	11	24/10/2022	08/11/2022
86	22.354	12	25/10/2022	09/11/2022		4
87	22.355	12	25/10/2022	09/11/2022
88	22.356	14	26/10/2022	15/11/2022		2
89	22.358	16	27/10/2022	14/11/2022		2
90	22.359	16	27/10/2022	14/11/2022
91	22.36	9	28/10/2022	10/11/2022		8
92	22.361	4	28/10/2022	16/11/2022		5
93	22.362	17	31/10/2022	18/11/2022		3
94	22.364	7	01/11/2022	16/11/2022		4
95	22.365	8	01/11/2022	17/11/2022		3
96	22.366	13	02/11/2022	17/11/2022		2
97	22.368	15	03/11/2022	07/12/2022		2
98	22.37	18	07/11/2022	22/11/2022		3
99	22.372	1	08/11/2022	21/11/2022		7
100	22.373	2	08/11/2022	21/11/2022		2
101	22.374	11	09/11/2022	23/11/2022		2
102	22.376	12	10/11/2022	25/11/2022		2
103	22.378	3	11/11/2022	24/11/2022		5
104	22.379	5	11/11/2022	29/11/2022		11
105	22.38	6	14/11/2022	28/11/2022		11
106	22.381	9	14/11/2022	28/11/2022		3
107	22.382	16	15/11/2022	01/12/2022		2
Sheet1

Cell Formulas
Range	Formula
G3:G4	G3	=IF(OR(F3="",MAXIFS($E2:E$3,$C2:C$3,C3,$F2:F$3,1)<1),"",NETWORKDAYS.INTL(MAXIFS($E2:E$3,$C2:C$3,C3,$F2:F$3,1),[@[brew date]],1))
G5:G107	G5	=IF(OR(F5="",MAXIFS($E$3:E4,$C$3:C4,C5,$F$3:F4,1)<1),"",NETWORKDAYS.INTL(MAXIFS($E$3:E4,$C$3:C4,C5,$F$3:F4,1),[@[brew date]],1))

Named Ranges
Name	Refers To	Cells
brew_date	=Sheet1!$D$3:$D$107	G3
empty_date	=Sheet1!$E$3:$E$107	G3:G107
tank_number	=Sheet1!$C$3:$C$107	G3:G107

From this table you should be able to create a Pivot Table, as you wanted.

 

[AllardZ]

Thank you so much! This helped me a great deal!

 

[Skybluekid]

Very welcome