Need help with a formula to divide allotted hours over a period of weeks.

[Irishmatty513]

I have a spreadsheet (attached) that takes user entered data (in the form of Allotted hours, weeks to complete, and due date), divides the due date by the number of weeks and returns the start date.
The area to the right, separated out by week columns, contain cells with IF statements that check the dates entered against the week in which they fall, filter themselves out or return how many hours fall within that week.
The problem that I am having is that if the start date falls in the middle of week, the cells return nothing and if the end date falls in the middle of a week, the cell returns the same amount of hours as the week before.
I have reached the bounds of my current ability with formulas and do not code, anyone able to give me some ideas on how to proceed?
Any help would be awesome.
Thanks

Matt

[Melvinrobb]

Is the attached what you are looking for?

[Irishmatty513]

It appears as though the formula is now returning only half of the allotted hours and still not reflecting in the correct columns.
I changed the hour value for "SHOW 4" to "10" and the result is 4 hours in the week of 5/6 and 1 in the week of 5/13 (total of 5 hours).
Also, the start date falls within the week of 4/29 and that cell has no returned value.
I am still looking through your formula, trying to wrap my head around it.
Thanks for looking at it!

[daddylonglegs]

Try this formula in G4 copied across and down

=$F4/$E4/7*MAX(0,MIN(G$3+7,$D4+1)-MAX(G$3,$C4+1))

You might have to adjust, depending on how you expect the days to fall, e.g. if 14 days is 1st to 15th May then you need to stipulate which day isn't included in the period, 1st or 15th?

[Irishmatty513]

Thank you daddylonglegs, worked perfectly!
Thanks!