make weekly report

[Kartiksvn]

date (autofill)​	name​	work hour​	pieces (number)​	note​
07/09/2020​	K​	10​	256​	day​
07/09/2020​	G​	11​	349​	night​
08/09/2020​	H​	9​	201​	day​
08/09/2020​	G​	11​	367​	night​
09/09/2020​	K​	10​	257​	day​
09/09/2020​	H​	10​	244​	day​

and still on.....
now I want formula which is shown weekly report (updating every day so I can see his last 7 days progress) in below table.

name	01/09	02/09	03/09	04/09	05/09	06/09	07/09	Avg.
K​	239	245	0	0	255	200	256	239
H​	259	256	0	356	345	300	0	303
G​	257	255	0	0	245	255	349	272

So, as you can see there are various types of formulas. like auto date which is daily changes and from that days total pieces and also average formula where calculate only workdays average.
and also operator do night and day both shifts also same day both pieces lots total also should shown in above table. I require date formula in above table which should changes daily. for example next day in heading, 02/09 to 08/09 date must show.

 

[Toadstool]

Hi Kariksvn,

I'm not sure what (autofill) means on the date field.

The calculation omits Saturday and Sunday. If you want to omit Holidays it will need to include a table of holiday dates.

I'm not sure how the night/day shift should change the calculation.

Here is the Data sheet:

Kartiksvn.xlsx
	A	B	C	D	E
1	date (autofill)	name	work hour	pieces (number)	note
2	01-Sep-20	K	10	256	day
3	01-Sep-20	G	11	349	night
4	02-Sep-20	H	9	201	day
5	02-Sep-20	G	11	367	night
6	03-Sep-20	K	10	257	day
7	03-Sep-20	H	10	244	day
Data

Here is the Average sheet with formulae

Kartiksvn.xlsx
	A	B	C	D	E	F	G	H	I
1		Tue	Wed	Thu	Fri	Sat	Sun	Mon
2	name	1-Sep	2-Sep	3-Sep	4-Sep	5-Sep	6-Sep	7-Sep	Avg.
3	K	256	0	257	0	0	0	0	102.60
4	H	0	201	244	0	0	0	0	89.00
5	G	349	367	0	0	0	0	0	143.20
Average

Cell Formulas
Range	Formula
B1:H1	B1	=TEXT(B2,"ddd")
B2	B2	=TODAY()-7
C2:H2	C2	=B2+1
B3:H5	B3	=SUMIFS(Data!$D:$D,Data!$B:$B,$A3,Data!$A:$A,B$2)
I3:I5	I3	=SUMIFS(B3:H3,$B$1:$H$1,"<>Sat",$B$1:$H$1,"<>Sun")/COUNTIFS($B$1:$H$1,"<>Sat",$B$1:$H$1,"<>Sun")

 

[Kartiksvn]

Thank you so much. It is extremely helpfull. Because I'm just beginner.