# Average hours per week

#### rgillson3

##### Board Regular
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

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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### rgillson3

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

#### Fluff

##### MrExcel MVP, Moderator
Ok for I4 use
Excel Formula:
``=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
Excel Formula:
``=SUMIFS(F:F,C:C,H5#,B:B,">"&I4#-7,B:B,"<="&I4#)``

#### Fluff

##### MrExcel MVP, Moderator
A slightly shorter formula for I4
Excel Formula:
``=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

##### Board Regular

The new date formula worked without a hitch.

The new sumifs formula is returning all zeroes.

#### Fluff

##### MrExcel MVP, Moderator
The new dates are the correct days, months & year?

#### rgillson3

##### Board Regular

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

#### Fluff

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

+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUV
22/10/2021 - 31/12/2021
3
4EmployeePayroll IDTot Hrs03/10/202110/10/202117/10/202124/10/202131/10/202107/11/202114/11/202121/11/202128/11/202105/12/202112/12/202119/12/202126/12/202102/01/2022
502/10/2021Jane Smith15115.82Jane Smith15.559.2726.5837.134.6729.5524.2928.1239.4330.936.1136.2226.9516.34
603/10/2021Jane Smith15119.73Jane Doe6.4511.2711.6512.268.333.70013.244.350000
705/10/2021Jane Smith15114.57BQT104 BANQUET1044.529.9215.1514.9812.2619.2717.2225.939.3316.2225.489.436.475.92
806/10/2021Jane Smith15114.7BQT105 BANQUET1055.1207.4812.723.358.36018.3709.2318.2423.159.40
911/10/2021Jane Smith15110.72
1015/10/2021Jane Smith15115.93
1116/10/2021Jane Smith151110.4
1217/10/2021Jane Smith15110.45
1317/10/2021Jane Smith15119.08
1419/10/2021Jane Smith15115.73
1520/10/2021Jane Smith15114.9
1622/10/2021Jane Smith15116.13
1723/10/2021Jane Smith151110.77
1824/10/2021Jane Smith15119.57
1926/10/2021Jane Smith15110.27
2026/10/2021Jane Smith15115.18
2127/10/2021Jane Smith15115.8
2229/10/2021Jane Smith15115.32
2330/10/2021Jane Smith15115.37
2430/10/2021Jane Smith15113.88
2531/10/2021Jane Smith15118.85
2602/11/2021Jane Smith15114.5
2703/11/2021Jane Smith15115
2806/11/2021Jane Smith151110.43
2907/11/2021Jane Smith15115.82
3007/11/2021Jane Smith15113.8
3109/11/2021Jane Smith15115.73
3213/11/2021Jane Smith151110.15
3314/11/2021Jane Smith15115.03
3414/11/2021Jane Smith15113.38
3517/11/2021Jane Smith15114.38
3619/11/2021Jane Smith15113
3720/11/2021Jane Smith15111.82
3820/11/2021Jane Smith15115.57
3920/11/2021Jane Smith15114.37
4021/11/2021Jane Smith15118.98
4124/11/2021Jane Smith15114.95
4225/11/2021Jane Smith15119.7
4326/11/2021Jane Smith15117.32
4427/11/2021Jane Smith15115.67
4527/11/2021Jane Smith15113.4
4628/11/2021Jane Smith15115.52
4728/11/2021Jane Smith15112.87
4830/11/2021Jane Smith15114.42
4901/12/2021Jane Smith15110.8
5001/12/2021Jane Smith15115.55
5104/12/2021Jane Smith151111.48
5205/12/2021Jane Smith15118.65
5307/12/2021Jane Smith15114.43
5408/12/2021Jane Smith15114.65
5510/12/2021Jane Smith15116.38
5611/12/2021Jane Smith151110.85
5712/12/2021Jane Smith15119.8
5814/12/2021Jane Smith15114.12
5915/12/2021Jane Smith15114.15
6017/12/2021Jane Smith15115.3
6118/12/2021Jane Smith151111.15
6219/12/2021Jane Smith151111.5
6321/12/2021Jane Smith15115.98
6422/12/2021Jane Smith15115.9
6524/12/2021Jane Smith15116.15
6626/12/2021Jane Smith15118.92
6728/12/2021Jane Smith15115.52
6829/12/2021Jane Smith15115.57
6931/12/2021Jane Smith15115.25
70391.08
7102/10/2021Jane Doe72113
7202/10/2021Jane Doe72113.45
7308/10/2021Jane Doe72115.25
7409/10/2021Jane Doe72113.17
7509/10/2021Jane Doe72112.85
7615/10/2021Jane Doe72114.53
7717/10/2021Jane Doe72114.42
7817/10/2021Jane Doe72112.7
7919/10/2021Jane Doe72113.72
8022/10/2021Jane Doe72114.22
8123/10/2021Jane Doe72114.32
8227/10/2021Jane Doe72113.73
8331/10/2021Jane Doe72114.6
8402/11/2021Jane Doe72113.7
8525/11/2021Jane Doe72114.87
8626/11/2021Jane Doe72113.82
8728/11/2021Jane Doe72114.55
8802/12/2021Jane Doe72110.48
8905/12/2021Jane Doe72113.87
9071.25
Main
Cell Formulas
RangeFormula
I4:V4I4=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:H8H5=UNIQUE(FILTER(C5:C170,C5:C170<>""))
I5:V8I5=SUMIFS(F:F,C:C,H5#,B:B,">"&I4#-7,B:B,"<="&I4#)
Dynamic array formulas.

#### rgillson3

##### Board Regular
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

##### MrExcel MVP, Moderator
That would suggest that you have the same problem with the dates as you had before.

Replies
16
Views
178
Replies
1
Views
223
Replies
1
Views
48
Replies
1
Views
320
Replies
5
Views
128

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,491
Messages
5,764,675
Members
425,229
Latest member
Rashid mahmood

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back