New Contributor

# Average Hours over last year - multiple job rows

Hi, looking for some guidance. I need to be able to show what the average contract hours for an employee are over the last 365 days. My issue is that they will likely have multiple employment rows each with differing hours.

I can work out the days & what portion of the gross hours they should have worded in that period but can't think how to sum these values over the last year only.

A sample of the source data is below. Any advice or guidance would be greatfully received!

 Employee Id Gross Hours Effective Date end 1 0 04/04/2022 03/04/2023 2 936 02/08/2022 31/03/2023 2 936 01/04/2023 03/04/2023 3 728 24/07/2021 30/09/2021 3 884 01/10/2021 31/12/2021 3 884 01/01/2022 10/09/2022 3 312 11/09/2022 30/09/2022 3 312 01/10/2022 21/11/2022 3 312 22/11/2022 31/03/2023 3 312 01/04/2023 03/04/2023 4 2080 15/11/2021 30/09/2022 4 2080 01/10/2022 30/11/2022 4 2080 01/12/2022 03/04/2023
3 Replies

# Re: Average Hours over last year - multiple job rows

I copied your data to Excel--some of the dates needed to be re-entered to conform with Excel's date formats.....

And then used this formula to yield the results shown below.

=AVERAGE(FILTER(Table1[Gross Hours],(Table1[Employee Id]=G4)*YEAR(Table1[Effective Date])=2022))

It would be easy to add the end date into the calculation as well (FILTER accommodates multiple criteria), but I don't really know what I'm looking at with your data.

It's hard to know what they mean because your sample data are--pardon me for saying it--odd. For example, employee ID 4 has gross hours of 2080 in each of the three rows, regardless of the dates associated. That may be accurate, but it makes it hard to test a formula to know if it's yielding useful results.

Anyway, the FILTER function does require Excel 2021 or newer. That link will take you to a good explanation of the function.

# Re: Average Hours over last year - multiple job rows

@mathetes Thank you very much. Re the data - each line is showing a role change -so some will be hours some will be wages, some job titles etc

# Re: Average Hours over last year - multiple job rows

I assume you're saying that the solution I gave worked. I'd appreciate, if it IS finished to your satisfaction, if you clicked on the "Mark as best response" link, so as to close it down,