Split total hours in a week to individual days

Hi All
Thanks in advance. Any help is appreciated and am new to VBA coding in Excel. Just learning stuffs from here.

I have a project to complete quickly. I have a source data that has the hours worked per employee that is grouped per week. The end week is always a Friday, and in separate columns I have from Sat - Fri the hours worked each day. I have to prepare a pivot with hours worked for each month. The problem is there will be an overlap of hours from two different months, i.e at the start of the month and the end of the month.

0DC29E66217F7A.jpg

Pls refer to my attached image.
I have the week ending date which is a friday in col F.
I have the hours from Sat - Fri in col AC through AI. (the date in column F and the date in col AI are same)
Col AQ onwards is prepared by me to add the hours manually depending on which month the hours fall.

I need a macro to get this job done and also this needs to be reused year after year (should be year independent and probably this file will be used for a year and the next year we will have a new one ). Currently I have written formulas that can be reused for a year. Is there a way?



Thanks