Splitting days in a week over a calendar month

Hi All,

This is a tricky one to explain but i'll try my best (working example attached albeit not automated).

So i'm using the weekday formula to allocate hours worked per day into weekly columns via sumif's - this is working ok.

Now the challenge comes in for weeks that falls over a calendar month end/start.

I'd like to be able to split out the day's within the week which straddles the two months, as per my example 2 days in Apr and 3 days in May. This would mean that the last week of apr will be light and the first week of may will be heavy.

Hope this makes sense and still toying with the idea of using this method or not but keen to hear your thoughts on this problem

Hi GTI,

Excel Pivot Tables has this covered. You group by dates and make the group length of 7 days. See the attached.

Weekly Allocation by days Group by 7 days.xlsx