Average hours per week

rgillson3

Board Regular
Joined
Oct 3, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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.
 

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The new date formula worked without a hitch.

The new sumifs formula is returning all zeroes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
The new dates are the correct days, months & year?
 

rgillson3

Board Regular
Joined
Oct 3, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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
1Minimum Wage Adjustment Report
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
Joined
Oct 3, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
That would suggest that you have the same problem with the dates as you had before.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top