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



## AllardZ (Dec 30, 2022)

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 numberbrew dateempty date22.2711715-08-2230-08-2222.2721715-08-2230-08-2222.2731816-08-2208-09-2222.2741816-08-2208-09-2222.2751317-08-2202-09-2222.2761317-08-2202-09-2222.277418-08-2201-09-2222.278119-08-2219-09-2222.279522-08-2206-09-2222.280622-08-2206-09-2222.2811623-08-2209-09-2222.2821623-08-2209-09-2222.2831524-08-2207-09-2222.2841524-08-2207-09-2222.2851425-08-2212-09-2222.2861425-08-2212-09-2222.2871129-08-2213-09-2222.2881129-08-2213-09-2222.2891230-08-2216-09-2222.2901230-08-2216-09-2222.2911731-08-2214-09-2222.2921731-08-2214-09-2222.293701-09-2215-09-2222.294805-09-2220-09-2222.295405-09-2219-09-2222.296906-09-2220-09-2222.2971306-09-2221-09-2222.2981306-09-2221-09-2222.299207-09-2222-09-2222.3001508-09-2223-09-2222.3011508-09-2223-09-2222.302609-09-2222-09-2222.3031612-09-2226-09-2222.3041612-09-2226-09-2222.3051813-09-2229-09-2222.3061813-09-2229-09-2222.307314-09-2227-09-2222.308514-09-2230-09-2222.3091115-09-2203-10-2222.3101115-09-2203-10-2222.311719-09-2207-10-2222.3121219-09-2204-10-2222.3131220-09-2204-10-2222.3141420-09-2205-10-2222.3151421-09-2205-10-2222.316821-09-2210-10-2222.317122-09-2206-10-2222.318422-09-2206-10-2222.3191726-09-2212-10-2222.3201726-09-2212-10-2222.3211327-09-2213-10-2222.3221327-09-2213-10-2222.3231529-09-2214-10-2222.3241529-09-2214-10-2222.325230-09-2217-10-2222.326630-09-2217-10-2222.327303-10-2219-10-2222.328904-10-2219-10-2222.3291105-10-2221-10-2222.3301206-10-2220-10-2222.3311206-10-2220-10-2222.332410-10-2224-10-2222.333710-10-2227-10-2222.3341411-10-2225-10-2222.3351411-10-2225-10-2222.3361612-10-2226-10-2222.3371612-10-2226-10-2222.3381713-10-2227-10-2222.3391713-10-2227-10-2222.340814-10-2228-10-2222.341514-10-2228-10-2222.342117-10-2231-10-2222.3431317-10-2201-11-2222.3441317-10-2201-11-2222.345618-10-2231-10-2222.3461519-10-2202-11-2222.3471519-10-2202-11-2222.3481820-10-2203-11-2222.3491820-10-2203-11-2222.350221-10-2207-11-2222.351321-10-2207-11-2222.3521124-10-2208-11-2222.3531124-10-228-11-2222.3541225-10-229-11-2222.3551225-10-229-11-2222.3561426-10-2215-11-2222.3571426-10-2215-11-2222.3581627-10-2214-11-2222.3591627-10-2214-11-2222.360928-10-2210-11-2222.361428-10-2216-11-2222.3621731-10-2218-11-2222.3631731-10-2218-11-2222.36471-11-2216-11-2222.36581-11-2217-11-2222.366132-11-2217-11-2222.367132-11-2217-11-2222.368153-11-227-12-2222.369153-11-227-12-2222.370187-11-2222-11-2222.371187-11-2222-11-2222.37218-11-2221-11-2222.37328-11-2221-11-2222.374119-11-2223-11-2222.375119-11-2223-11-2222.3761210-11-2225-11-2222.3771210-11-2225-11-2222.378311-11-2224-11-2222.379511-11-2229-11-2222.380614-11-2228-11-2222.381914-11-2228-11-2222.3821615-11-221-12-2222.3831615-11-221-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 (Dec 30, 2022)

Hi

What version of Excel are you using?

KR


----------



## Skybluekid (Dec 30, 2022)

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 (Jan 2, 2023)

Skybluekid said:


> I have created a table for all the tank numbers and Inserted formulas to get the information
> 
> View attachment 81792
> 
> ...


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 (Jan 3, 2023)

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.xlsxBCDEG2batch #tank numberbrew dateempty dateDays322.2711715/08/202230/08/2022-12422.2721715/08/202230/08/2022 522.2731816/08/202208/09/2022 622.2741816/08/202208/09/2022 722.2751317/08/202202/09/2022 822.2761317/08/202202/09/2022 922.277418/08/202201/09/2022 1022.278119/08/202219/09/2022 1122.279522/08/202206/09/2022 1222.28622/08/202206/09/2022 1322.2811623/08/202209/09/2022 1422.2821623/08/202209/09/2022 1522.2831524/08/202207/09/2022 1622.2841524/08/202207/09/2022 1722.2851425/08/202212/09/2022 1822.2861425/08/202212/09/2022 1922.2871129/08/202213/09/2022 2022.2881129/08/202213/09/2022 2122.2891230/08/202216/09/2022 2222.291230/08/202216/09/2022 2322.2911731/08/202214/09/202222422.2921731/08/202214/09/2022 2522.293701/09/202215/09/2022 2622.294805/09/202220/09/2022 2722.295405/09/202219/09/202232822.296906/09/202220/09/2022 2922.2971306/09/202221/09/202233022.2981306/09/202221/09/2022 3122.299207/09/202222/09/2022 3222.31508/09/202223/09/202223322.3011508/09/202223/09/2022 3422.302609/09/202222/09/202243522.3031612/09/202226/09/202223622.3041612/09/202226/09/2022 3722.3051813/09/202229/09/202243822.3061813/09/202229/09/2022 3922.307314/09/202227/09/2022 4022.308514/09/202230/09/202274122.3091115/09/202203/10/202234222.311115/09/202203/10/2022 4322.311719/09/202207/10/202234422.3121219/09/202204/10/202224522.3131220/09/202204/10/2022-114622.3141420/09/202205/10/202274722.3151421/09/202205/10/2022-114822.316821/09/202210/10/202224922.317122/09/202206/10/202245022.318422/09/202206/10/202245122.3191726/09/202212/10/202295222.321726/09/202212/10/2022 5322.3211327/09/202213/10/202255422.3221327/09/202213/10/2022 5522.3231529/09/202214/10/202255622.3241529/09/202214/10/2022 5722.325230/09/202217/10/202275822.326630/09/202217/10/202275922.327303/10/202219/10/202256022.328904/10/202219/10/2022116122.3291105/10/202221/10/202236222.331206/10/202220/10/202236322.3311206/10/202220/10/2022 6422.332410/10/202224/10/202236522.333710/10/202227/10/202226622.3341411/10/202225/10/202256722.3351411/10/202225/10/2022 6822.3361612/10/202226/10/2022136922.3371612/10/202226/10/2022 7022.3381713/10/202227/10/202227122.3391713/10/202227/10/2022 7222.34814/10/202228/10/202257322.341514/10/202228/10/2022117422.342117/10/202231/10/202287522.3431317/10/202201/11/202237622.3441317/10/202201/11/2022 7722.345618/10/202231/10/202227822.3461519/10/202202/11/202247922.3471519/10/202202/11/2022 8022.3481820/10/202203/11/2022168122.3491820/10/202203/11/2022 8222.35221/10/202207/11/202258322.351321/10/202207/11/202238422.3521124/10/202208/11/202228522.3531124/10/202208/11/2022 8622.3541225/10/202209/11/202248722.3551225/10/202209/11/2022 8822.3561426/10/202215/11/202228922.3581627/10/202214/11/202229022.3591627/10/202214/11/2022 9122.36928/10/202210/11/202289222.361428/10/202216/11/202259322.3621731/10/202218/11/202239422.364701/11/202216/11/202249522.365801/11/202217/11/202239622.3661302/11/202217/11/202229722.3681503/11/202207/12/202229822.371807/11/202222/11/202239922.372108/11/202221/11/2022710022.373208/11/202221/11/2022210122.3741109/11/202223/11/2022210222.3761210/11/202225/11/2022210322.378311/11/202224/11/2022510422.379511/11/202229/11/20221110522.38614/11/202228/11/20221110622.381914/11/202228/11/2022310722.3821615/11/202201/12/20222Sheet1Cell FormulasRangeFormulaG3:G4G3=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:G107G5=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 RangesNameRefers ToCellsbrew_date=Sheet1!$D$3:$D$107G3empty_date=Sheet1!$E$3:$E$107G3:G107tank_number=Sheet1!$C$3:$C$107G3:G107

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


----------



## AllardZ (Jan 4, 2023)

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


----------



## Skybluekid (Jan 4, 2023)

Very welcome


----------

