Formula to Determine Average Hours Worked per Month for People on My Team

[lneidorf]

Hi there.

I've spent way too much time on this and am probably overthinking it.

I've got raw data with individual employee time entries. Multiple entries per employee per day. Employees on my team plus employees on other teams are included. There's a column that indicates the team.

What I'm hoping to come up with is a monthly average total hours worked across my team. This way, I can determine who's putting in too many hours and who's putting in too few so that I can rebalance workload accordingly.

I'm including a screenshot with a simplified representation of the raw data and the result I'm looking for. Raw data on the left; Result on the right. Ultimately, I need a formula that yields the results in column J.

Many thanks.

 

[Joe4]

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.

 

[lneidorf]

Thank you, Joe4. Unfortunately I won't be able to install this add-in.

And so the best thing I can think to do is to post the data as a table:

Date	Employee	Hours	Team
7/1/2022​	John	0.8	My Team
7/1/2022​	Paul	0.8	My Team
7/1/2022​	Mick	0.8	Other Team
7/1/2022​	Keith	0.6	Other Team
7/2/2022​	John	2.3	My Team
7/2/2022​	Paul	1.3	My Team
7/2/2022​	Mick	1.4	Other Team
7/2/2022​	Keith	0.5	Other Team
7/3/2022​	John	1.6	My Team
7/3/2022​	Paul	0.9	My Team
7/3/2022​	Mick	0.4	Other Team
7/3/2022​	Keith	0.2	Other Team
8/1/2022​	John	0.9	My Team
8/1/2022​	Paul	0.5	My Team
8/1/2022​	Mick	0.3	Other Team
8/1/2022​	Keith	2.0	Other Team
8/2/2022​	John	1.9	My Team
8/2/2022​	Paul	0.7	My Team
8/2/2022​	Mick	1.0	Other Team
8/2/2022​	Keith	0.8	Other Team
8/3/2022​	John	1.1	My Team
8/3/2022​	Paul	0.9	My Team
8/3/2022​	Mick	1.1	Other Team
8/3/2022​	Keith	2.5	Other Team

 

[Fluff]

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’)

 

[Joe4]

OK, making the following assumptions:
1. Your values in column A are valid dates.
2. You will not have two different years of the same month in the table (i.e. for July, only have July 2022 records in table and not July 2023 records).
3. Your Table name is "Table2" (adjust in formulas to suit)

Here are formulas you can use:

In cell G3:

=SUMPRODUCT(--(Table2[Team]="My Team"),--(TEXT(Table2[Date],"mmmm")=$F3),--(Table2[Employee]=G$2),Table2[Hours])

then copy to cell H3

In cell I3:

=H3+G3

In cell J3:

=AVERAGE(G3:H3)

And then copy these formulas in G3:J3 down all your rows as far as you need.

This yielded for me the exact expected values you show.

 

[lneidorf]

OK, making the following assumptions:
1. Your values in column A are valid dates.
2. You will not have two different years of the same month in the table (i.e. for July, only have July 2022 records in table and not July 2023 records).
3. Your Table name is "Table2" (adjust in formulas to suit)

Here are formulas you can use:

In cell G3:

=SUMPRODUCT(--(Table2[Team]="My Team"),--(TEXT(Table2[Date],"mmmm")=$F3),--(Table2[Employee]=G$2),Table2[Hours])

then copy to cell H3

In cell I3:

=H3+G3

In cell J3:

=AVERAGE(G3:H3)

And then copy these formulas in G3:J3 down all your rows as far as you need.

This yielded for me the exact expected values you show.

Thanks so much for this, Joe4.

This is actually more than is required. I included columns G:I to show my calculations, but only care about the result column J. I need just one number per month, restricted to My Team.

I'll play around with what you showed me, but would be grateful for any thoughts you might have.

Many thanks!

 

[lneidorf]

Thanks so much for this, Joe4.

This is actually more than is required. I included columns G:I to show my calculations, but only care about the result column J. I need just one number per month, restricted to My Team.

I'll play around with what you showed me, but would be grateful for any thoughts you might have.

Many thanks!

Ok, I got this almost figured out. One last wrinkle: is there a way to adapt your formula to be year-specific? At some point, I do think the raw data will span multiple years, in which case, there will be the same months across years. Thanks again!

 

[Joe4]

Ok, I got this almost figured out. One last wrinkle: is there a way to adapt your formula to be year-specific? At some point, I do think the raw data will span multiple years, in which case, there will be the same months across years. Thanks again!

You will need to include year along with month in your criteria (i.e. a text value of "July 2022" in your heading/criteria), and then amend this part:

--(TEXT(Table2[Date],"mmmm")=$F3)

to share year too, like this:

--(TEXT(Table2[Date],"mmmm yyyy")=$F3)

 

[lneidorf]

You will need to include year along with month in your criteria (i.e. a text value of "July 2022" in your heading/criteria), and then amend this part:

--(TEXT(Table2[Date],"mmmm")=$F3)

to share year too, like this:

--(TEXT(Table2[Date],"mmmm yyyy")=$F3)

Fantastic. Thanks so much, @Joe4 !

 

[Joe4]

You are welcome.