Average hours per week

[rgillson3]

I'm trying to find a simpler/quicker way to obtain the average number of hours per week for employees.

My spreadsheet contains columns for Date, Employee Name, Employee Number and Hours.

 

[rgillson3]

Assuming the calculations are using that as the week ending date, yes.

 

[rgillson3]

Yes, assuming the calculations use those as week ending dates.

 

[Fluff]

Ok for I4 use

=LET(sDte,DATEVALUE(LEFT(E2,10)),eDte,DATEVALUE(RIGHT(E2,10)),Start,sDte+7-WEEKDAY(sDte,2),End,eDte+7-WEEKDAY(eDte,2),SEQUENCE(,ROUNDUP((End-Start+1)/7,0),Start,7))

and for I5

=SUMIFS(F:F,C:C,H5#,B:B,">"&I4#-7,B:B,"<="&I4#)

 

[Fluff]

A slightly shorter formula for I4

=LET(Start,WORKDAY.INTL(DATEVALUE(LEFT(E2,10))-1,1,"1111110"),SEQUENCE(,ROUNDUP((WORKDAY.INTL(DATEVALUE(RIGHT(E2,10))-1,1,"1111110")-Start+1)/7,0),Start,7))

 

[rgillson3]

The new date formula worked without a hitch.

The new sumifs formula is returning all zeroes.

 

[Fluff]

The new dates are the correct days, months & year?

 

[rgillson3]

Yes, the dates are the correct, days, months & year.

 

[Fluff]

In that case I'm at a loss as to why the sumifs is returning 0s when it was working before.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1					Minimum Wage Adjustment Report
2					2/10/2021 - 31/12/2021
3
4			Employee		Payroll ID	Tot Hrs			03/10/2021	10/10/2021	17/10/2021	24/10/2021	31/10/2021	07/11/2021	14/11/2021	21/11/2021	28/11/2021	05/12/2021	12/12/2021	19/12/2021	26/12/2021	02/01/2022
5		02/10/2021	Jane Smith		1511	5.82		Jane Smith	15.55	9.27	26.58	37.1	34.67	29.55	24.29	28.12	39.43	30.9	36.11	36.22	26.95	16.34
6		03/10/2021	Jane Smith		1511	9.73		Jane Doe	6.45	11.27	11.65	12.26	8.33	3.7	0	0	13.24	4.35	0	0	0	0
7		05/10/2021	Jane Smith		1511	4.57		BQT104 BANQUET104	4.52	9.92	15.15	14.98	12.26	19.27	17.22	25.93	9.33	16.22	25.48	9.43	6.47	5.92
8		06/10/2021	Jane Smith		1511	4.7		BQT105 BANQUET105	5.12	0	7.48	12.72	3.35	8.36	0	18.37	0	9.23	18.24	23.15	9.4	0
9		11/10/2021	Jane Smith		1511	0.72
10		15/10/2021	Jane Smith		1511	5.93
11		16/10/2021	Jane Smith		1511	10.4
12		17/10/2021	Jane Smith		1511	0.45
13		17/10/2021	Jane Smith		1511	9.08
14		19/10/2021	Jane Smith		1511	5.73
15		20/10/2021	Jane Smith		1511	4.9
16		22/10/2021	Jane Smith		1511	6.13
17		23/10/2021	Jane Smith		1511	10.77
18		24/10/2021	Jane Smith		1511	9.57
19		26/10/2021	Jane Smith		1511	0.27
20		26/10/2021	Jane Smith		1511	5.18
21		27/10/2021	Jane Smith		1511	5.8
22		29/10/2021	Jane Smith		1511	5.32
23		30/10/2021	Jane Smith		1511	5.37
24		30/10/2021	Jane Smith		1511	3.88
25		31/10/2021	Jane Smith		1511	8.85
26		02/11/2021	Jane Smith		1511	4.5
27		03/11/2021	Jane Smith		1511	5
28		06/11/2021	Jane Smith		1511	10.43
29		07/11/2021	Jane Smith		1511	5.82
30		07/11/2021	Jane Smith		1511	3.8
31		09/11/2021	Jane Smith		1511	5.73
32		13/11/2021	Jane Smith		1511	10.15
33		14/11/2021	Jane Smith		1511	5.03
34		14/11/2021	Jane Smith		1511	3.38
35		17/11/2021	Jane Smith		1511	4.38
36		19/11/2021	Jane Smith		1511	3
37		20/11/2021	Jane Smith		1511	1.82
38		20/11/2021	Jane Smith		1511	5.57
39		20/11/2021	Jane Smith		1511	4.37
40		21/11/2021	Jane Smith		1511	8.98
41		24/11/2021	Jane Smith		1511	4.95
42		25/11/2021	Jane Smith		1511	9.7
43		26/11/2021	Jane Smith		1511	7.32
44		27/11/2021	Jane Smith		1511	5.67
45		27/11/2021	Jane Smith		1511	3.4
46		28/11/2021	Jane Smith		1511	5.52
47		28/11/2021	Jane Smith		1511	2.87
48		30/11/2021	Jane Smith		1511	4.42
49		01/12/2021	Jane Smith		1511	0.8
50		01/12/2021	Jane Smith		1511	5.55
51		04/12/2021	Jane Smith		1511	11.48
52		05/12/2021	Jane Smith		1511	8.65
53		07/12/2021	Jane Smith		1511	4.43
54		08/12/2021	Jane Smith		1511	4.65
55		10/12/2021	Jane Smith		1511	6.38
56		11/12/2021	Jane Smith		1511	10.85
57		12/12/2021	Jane Smith		1511	9.8
58		14/12/2021	Jane Smith		1511	4.12
59		15/12/2021	Jane Smith		1511	4.15
60		17/12/2021	Jane Smith		1511	5.3
61		18/12/2021	Jane Smith		1511	11.15
62		19/12/2021	Jane Smith		1511	11.5
63		21/12/2021	Jane Smith		1511	5.98
64		22/12/2021	Jane Smith		1511	5.9
65		24/12/2021	Jane Smith		1511	6.15
66		26/12/2021	Jane Smith		1511	8.92
67		28/12/2021	Jane Smith		1511	5.52
68		29/12/2021	Jane Smith		1511	5.57
69		31/12/2021	Jane Smith		1511	5.25
70						391.08
71		02/10/2021	Jane Doe		7211	3
72		02/10/2021	Jane Doe		7211	3.45
73		08/10/2021	Jane Doe		7211	5.25
74		09/10/2021	Jane Doe		7211	3.17
75		09/10/2021	Jane Doe		7211	2.85
76		15/10/2021	Jane Doe		7211	4.53
77		17/10/2021	Jane Doe		7211	4.42
78		17/10/2021	Jane Doe		7211	2.7
79		19/10/2021	Jane Doe		7211	3.72
80		22/10/2021	Jane Doe		7211	4.22
81		23/10/2021	Jane Doe		7211	4.32
82		27/10/2021	Jane Doe		7211	3.73
83		31/10/2021	Jane Doe		7211	4.6
84		02/11/2021	Jane Doe		7211	3.7
85		25/11/2021	Jane Doe		7211	4.87
86		26/11/2021	Jane Doe		7211	3.82
87		28/11/2021	Jane Doe		7211	4.55
88		02/12/2021	Jane Doe		7211	0.48
89		05/12/2021	Jane Doe		7211	3.87
90						71.25
Main

Cell Formulas
Range	Formula
I4:V4	I4	=LET(sDte,DATEVALUE(LEFT(E2,10)),eDte,DATEVALUE(RIGHT(E2,10)),Start,sDte+7-WEEKDAY(sDte,2),End,eDte+7-WEEKDAY(eDte,2),SEQUENCE(,ROUNDUP((End-Start+1)/7,0),Start,7))
H5:H8	H5	=UNIQUE(FILTER(C5:C170,C5:C170<>""))
I5:V8	I5	=SUMIFS(F:F,C:C,H5#,B:B,">"&I4#-7,B:B,"<="&I4#)
Dynamic array formulas.

 

[rgillson3]

I was originally working with a "fresh" spreadsheet. So, I took a chance and copied the formulas from the new spreadsheet to the old one, and they work!

 

[Fluff]

That would suggest that you have the same problem with the dates as you had before.