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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you post some sample data to the board.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

rgillson3

Board Regular
Joined
Oct 3, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows
Ok. If I following the instructions correctly, this should provide you with a copy of my worksheet.

Average Hours Per Week.xlsx
BCDEF
1Minimum Wage Adjustment Report
210/2/2021 - 12/31/2021
3
4EmployeePayroll IDTot Hrs
510/02/2021Jane Smith15115.82
610/03/2021Jane Smith15119.73
710/05/2021Jane Smith15114.57
810/06/2021Jane Smith15114.70
910/15/2021Jane Smith15110.72
1010/15/2021Jane Smith15115.93
1110/16/2021Jane Smith151110.40
1210/17/2021Jane Smith15110.45
1310/17/2021Jane Smith15119.08
1410/19/2021Jane Smith15115.73
1510/20/2021Jane Smith15114.90
1610/22/2021Jane Smith15116.13
1710/23/2021Jane Smith151110.77
1810/24/2021Jane Smith15119.57
1910/26/2021Jane Smith15110.27
2010/26/2021Jane Smith15115.18
2110/27/2021Jane Smith15115.80
2210/29/2021Jane Smith15115.32
2310/30/2021Jane Smith15115.37
2410/30/2021Jane Smith15113.88
2510/31/2021Jane Smith15118.85
2611/02/2021Jane Smith15114.50
2711/03/2021Jane Smith15115.00
2811/06/2021Jane Smith151110.43
2911/07/2021Jane Smith15115.82
3011/07/2021Jane Smith15113.80
3111/09/2021Jane Smith15115.73
3211/13/2021Jane Smith151110.15
3311/14/2021Jane Smith15115.03
3411/14/2021Jane Smith15113.38
3511/17/2021Jane Smith15114.38
3611/19/2021Jane Smith15113.00
3711/20/2021Jane Smith15111.82
3811/20/2021Jane Smith15115.57
3911/20/2021Jane Smith15114.37
4011/21/2021Jane Smith15118.98
4111/24/2021Jane Smith15114.95
4211/25/2021Jane Smith15119.70
4311/26/2021Jane Smith15117.32
4411/27/2021Jane Smith15115.67
4511/27/2021Jane Smith15113.40
4611/28/2021Jane Smith15115.52
4711/28/2021Jane Smith15112.87
4811/30/2021Jane Smith15114.42
4912/01/2021Jane Smith15110.80
5012/01/2021Jane Smith15115.55
5112/04/2021Jane Smith151111.48
5212/05/2021Jane Smith15118.65
5312/07/2021Jane Smith15114.43
5412/08/2021Jane Smith15114.65
5512/10/2021Jane Smith15116.38
5612/11/2021Jane Smith151110.85
5712/12/2021Jane Smith15119.80
5812/14/2021Jane Smith15114.12
5912/15/2021Jane Smith15114.15
6012/17/2021Jane Smith15115.30
6112/18/2021Jane Smith151111.15
6212/19/2021Jane Smith151111.50
6312/21/2021Jane Smith15115.98
6412/22/2021Jane Smith15115.90
6512/24/2021Jane Smith15116.15
6612/26/2021Jane Smith15118.92
6712/28/2021Jane Smith15115.52
6812/29/2021Jane Smith15115.57
6912/31/2021Jane Smith15115.25
70391.08
7110/02/2021Jane Doe72113.00
7210/02/2021Jane Doe72113.45
7310/08/2021Jane Doe72115.25
7410/09/2021Jane Doe72113.17
7510/09/2021Jane Doe72112.85
7610/15/2021Jane Doe72114.53
7710/17/2021Jane Doe72114.42
7810/17/2021Jane Doe72112.70
7910/19/2021Jane Doe72113.72
8010/22/2021Jane Doe72114.22
8110/23/2021Jane Doe72114.32
8210/27/2021Jane Doe72113.73
8310/31/2021Jane Doe72114.60
8411/02/2021Jane Doe72113.70
8511/25/2021Jane Doe72114.87
8611/26/2021Jane Doe72113.82
8711/28/2021Jane Doe72114.55
8812/02/2021Jane Doe72110.48
8912/05/2021Jane Doe72113.87
9071.25
9110/02/2021BQT104 BANQUET1041044.52
9210/08/2021BQT104 BANQUET1041041.57
9310/09/2021BQT104 BANQUET1041048.35
9410/13/2021BQT104 BANQUET1041041.88
9510/14/2021BQT104 BANQUET1041042.70
9610/15/2021BQT104 BANQUET1041041.77
9710/16/2021BQT104 BANQUET1041042.63
9810/17/2021BQT104 BANQUET1041046.17
9910/19/2021BQT104 BANQUET1041043.10
10010/20/2021BQT104 BANQUET1041040.42
10110/21/2021BQT104 BANQUET1041042.48
10210/22/2021BQT104 BANQUET1041041.65
10310/23/2021BQT104 BANQUET1041044.78
10410/24/2021BQT104 BANQUET1041042.55
10510/26/2021BQT104 BANQUET1041042.85
10610/27/2021BQT104 BANQUET1041045.83
10710/29/2021BQT104 BANQUET1041043.58
10811/02/2021BQT104 BANQUET1041043.30
10911/04/2021BQT104 BANQUET1041048.97
11011/05/2021BQT104 BANQUET1041044.78
11111/06/2021BQT104 BANQUET1041042.22
11211/09/2021BQT104 BANQUET1041044.02
11311/12/2021BQT104 BANQUET1041042.20
11411/13/2021BQT104 BANQUET10410411.00
11511/16/2021BQT104 BANQUET1041042.55
11611/17/2021BQT104 BANQUET1041042.82
11711/18/2021BQT104 BANQUET1041043.67
11811/19/2021BQT104 BANQUET1041044.00
11911/20/2021BQT104 BANQUET1041048.57
12011/21/2021BQT104 BANQUET1041044.32
12111/24/2021BQT104 BANQUET1041049.33
12212/01/2021BQT104 BANQUET1041043.47
12312/03/2021BQT104 BANQUET1041048.78
12412/04/2021BQT104 BANQUET1041043.97
12512/07/2021BQT104 BANQUET1041042.87
12612/08/2021BQT104 BANQUET1041043.73
12712/09/2021BQT104 BANQUET1041042.20
12812/10/2021BQT104 BANQUET1041044.78
12912/11/2021BQT104 BANQUET1041044.33
13012/12/2021BQT104 BANQUET1041047.57
13112/14/2021BQT104 BANQUET1041043.15
13212/15/2021BQT104 BANQUET1041042.58
13312/16/2021BQT104 BANQUET1041040.97
13412/17/2021BQT104 BANQUET1041042.73
13512/21/2021BQT104 BANQUET1041043.38
13612/22/2021BQT104 BANQUET1041043.07
13712/23/2021BQT104 BANQUET1041040.02
13812/28/2021BQT104 BANQUET1041043.13
13912/29/2021BQT104 BANQUET1041040.17
14012/30/2021BQT104 BANQUET1041042.62
141192.10
14210/02/2021BQT105 BANQUET1051055.12
14310/14/2021BQT105 BANQUET1051057.48
14410/21/2021BQT105 BANQUET1051053.00
14510/23/2021BQT105 BANQUET1051054.55
14610/24/2021BQT105 BANQUET1051055.17
14710/26/2021BQT105 BANQUET1051053.35
14811/02/2021BQT105 BANQUET1051051.73
14911/04/2021BQT105 BANQUET1051052.33
15011/05/2021BQT105 BANQUET1051054.30
15111/16/2021BQT105 BANQUET1051052.72
15211/17/2021BQT105 BANQUET1051053.65
15311/19/2021BQT105 BANQUET1051052.18
15411/20/2021BQT105 BANQUET1051053.80
15511/21/2021BQT105 BANQUET1051056.02
15612/02/2021BQT105 BANQUET1051053.27
15712/03/2021BQT105 BANQUET1051053.88
15812/04/2021BQT105 BANQUET1051052.08
15912/09/2021BQT105 BANQUET1051053.67
16012/10/2021BQT105 BANQUET1051053.70
16112/11/2021BQT105 BANQUET1051056.15
16212/12/2021BQT105 BANQUET1051054.72
16312/14/2021BQT105 BANQUET1051053.75
16412/15/2021BQT105 BANQUET1051053.60
16512/16/2021BQT105 BANQUET1051053.55
16612/17/2021BQT105 BANQUET1051054.87
16712/18/2021BQT105 BANQUET1051057.38
16812/21/2021BQT105 BANQUET1051054.93
16912/23/2021BQT105 BANQUET1051050.12
17012/29/2021BQT105 BANQUET1051054.35
171115.42
Minimum Wage Adjustment
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
Thanks for that.
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRST
1Minimum Wage Adjustment Report
210/2/2021 - 12/31/2021
3
4EmployeePayroll IDTot Hrs02/10/202109/10/202116/10/202123/10/202130/10/202106/11/202113/11/202120/11/202127/11/202104/12/202111/12/202118/12/2021
502/10/2021Jane Smith15115.82Jane Smith24.826.6536.6936.9127.625.7825.9442.7128.2335.5934.2240.68
603/10/2021Jane Smith15119.73Jane Doe11.710.5515.068.058.3008.695.033.8700
705/10/2021Jane Smith15114.57BQT104 BANQUET1046.0914.716.4519.5917.058.4424.0422.2212.2517.5521.336.47
806/10/2021Jane Smith15114.7BQT105 BANQUET1055.127.48313.078.3608.559.827.159.4526.6412.43
915/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:T4I4=SEQUENCE(,(MAX(B5:B170)-MIN(B5:B170))/7,MIN(B5:B170),7)
H5:H8H5=UNIQUE(FILTER(C5:C170,C5:C170<>""))
I5:T8I5=SUMIFS(F:F,C:C,H5#,B:B,">="&I4#,B:B,"<"&I4#+7)
Dynamic array formulas.
 

rgillson3

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

ADVERTISEMENT

I think that might work. But, a #CALC! err for the first date, which I think is affecting the sumifs functions. I've tried entering it as an array formula, and no change.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
Check that your dates are real dates & not text.
 

rgillson3

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

ADVERTISEMENT

Originally, they were/are formatted as general.

I found that if I select the range of dates and use Text to Columns, it will quickly convert them all.

I'm now seeing two "odd" things going on.

1 - When I look at the formula at I$ in the formula bar, it is black as usual. But, J-T4 are more grayed. Plus, I can't take the formula beyond 12/18/2021, even though there are definitely dates beyond that.

2 - Only the sumifs formula at I5 is showing an amount. I tried to make the references absolute with $, and it doesn't help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
When I look at the formula at I$ in the formula bar, it is black as usual. But, J-T4 are more grayed.
That's normal for a spill formula as the formula only really exists in the 1st cell.

For the formula in I4 try
Excel Formula:
=SEQUENCE(,ROUNDUP((MAX(B5:B170)-MIN(B5:B170)+1)/7,0),MIN(B5:B170),7)

Check that the numbers in col F are real numbers & not text.
 

rgillson3

Board Regular
Joined
Oct 3, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows
That formula gives me one more date, which will work with this set of data. Is there a way to increase for longer date ranges?

The number are initially "Custom". When I change them to numbers, the sumifs still do not show anything.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
It should run till the latest date in the range.
In G5 put
Excel Formula:
=ISNUMBER(F5:F170)
do all cells return true?
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,467
Messages
5,764,494
Members
425,219
Latest member
datdanigg

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