Pull Next Date if SUM is zero

[Nanaia]

I'm trying to reduce the size of the number of formulas used in a file.

I have data entry that enters multiple rows of data that make up different product lines and different shifts per day (the DATA tab). The number of formulas used to total each product line to determine the number produced per day is something I'd like to reduce (the TABLE tab). The DATA entered is only for the days worked (between one and four rows of data). The TABLE I created to summarize and calculate the daily data has every day of the year in the first column because production for these lines isn't necessarily done every day the business is open. Is there a way to SUM each days production without having to know the date in advance (I use SUMIFS)? As I mentioned, I have every day of the year listed in COL A. I would like to reduce it to be only the days there are production for the lines I'm tracking so I can remove all of the days of zero production. I'm hoping it helps reduce the file size.

A bit of background: this file is on the company's SharePoint server. I found out (the hard way) that I cannot divide the file into a DATA file and link it to a TABLE/DASHBOARD file to reduce the size of the data entry file. My hope is that reducing the number of lines containing non value calculations I can reduce the file size.

 

[Nanaia]

Since all the formulas look at the date column then provide the results for that date I was thinking to find a way to incorporate it in the TABLE date column. Like if the previous date line in the TABLE was 2/7/2020 and in the DATA tab there's no information for 2/8/2020 or 2/9/2020 but there is for 2/10/2020 then it puts 2/10/2020 in the TABLE date column. But I don't know if that's possible.

 

[jasonb75]

It would be possible, but practical is another thing entirely. You might find that the extra complexity of the formulas involved would make it less efficient than having the extra rows with 0 value results.

 

[sandy666]

there is wrong date

GA

14/02/2020​

NIGHT

B

200​

16:22:00​

02:43:00​

Y

Y

MINI

because End Time is from the next day: 15/02/2020
night of 14/02/2020 ending at 23:59:59
but maybe I am wrong ?

 

[Nanaia]

there is wrong date

GA

14/02/2020​

NIGHT

B

200​

16:22:00​

02:43:00​

Y

Y

MINI

because End Time is from the next day: 15/02/2020
night of 14/02/2020 ending at 23:59:59
but maybe I am wrong ?

The formula used to catch that is [=IF(G3<F3,G3+1,G3)-F3]. Start Time is Col F, End Time is Col G.

 

[sandy666]

I did something like that but in the meantime I got lost

S_Date	SITE	MD	CD	AB	Duration	A	B	C	D	MINI	COMM
14/02/2020	AZ	60			0.03:45:00	0	0	1	0	1	0
14/02/2020	GA	200			0.10:21:00	0	1	0	0	1	0
15/02/2020	GA	85			0.05:50:00	1	0	0	0	1	0
18/02/2020	GA	211	37		0.16:22:00	0	1	0	1	1	1
18/02/2020	KY	117			0.10:00:00	1	0	0	0	1	0
18/02/2020	TX	390	42		1.05:55:00	1	0	1	0	1	1
19/02/2020	GA		58		0.09:16:00	0	0	0	1	0	1
19/02/2020	TX	235	1		0.10:57:00	1	0	1	0	1	1
20/02/2020	AZ	164	16		0.11:58:00	0	1	1	0	1	1
24/02/2020	GA	552			1.01:08:00	1	1	0	0	1	0