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.

B	C	D	E	F	G	H	I	J	K
1	9/1/19	9/8/19	9/15/19	9/22/19	9/29/19	10/6/19	10/13/19	10/20/19	10/27/19
8	137.85	137.85	137.85	136.05	138.25	138.15	139.05	137.25	137.15

<tbody>
</tbody>

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)