Converting weekly data to monthly average

[lucystewart]

I have a staffing worksheet that gives me FTEs by week which I need for staffing but I need to submit my FTEs by month for budgeting reasons.
I can go through an do this by hand, averaging the weeks together but I am wondering if there is an easier/cleaner way to do this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/1/19[/TD]
[TD]9/8/19[/TD]
[TD]9/15/19[/TD]
[TD]9/22/19[/TD]
[TD]9/29/19[/TD]
[TD]10/6/19[/TD]
[TD]10/13/19[/TD]
[TD]10/20/19[/TD]
[TD]10/27/19[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]137.85[/TD]
[TD]137.85[/TD]
[TD]137.85[/TD]
[TD]136.05[/TD]
[TD]138.25[/TD]
[TD]138.15[/TD]
[TD]139.05[/TD]
[TD]137.25[/TD]
[TD]137.15[/TD]
[/TR]
</tbody>[/TABLE]

The tricky thing is that some months will have 4 columns and some months will have 5 columns depending on when the week falls. I would like them to average into whatever month the column is labeled with. Meaning Column G should fall into Sept even though technically only 2 days of that week are in September.
So, to clarify in the example above, I would like the average of C8:G8 and H8:K8. If possible I would like to be able to do this for a full fiscal year.

Thank you for any help you can provide.
Lucy

 

[steve the fish]

It depends how you are going to 'tell' the formula what dates you need. Lets say you have the 9/1/19 in cell M1. Try this:

=AVERAGEIFS($B$8:$J$8,$B$1:$J$1,">="&M1,$B$1:$J$1,"<"&EOMONTH(M1,0)+1)