Puzzling Formula With Several Variables

I'm trying to put together a bit of a report, and I'm finding it somewhat of a puzzle that I can't quite solve. There seems to be too many variables for me to wrap my brain around! I'm sure that what I am trying to achieve is possible, but I just can't put together the puzzle.

I'm trying to work out an average target number of hours per day for a number of individuals over a given date range. Previously this was easy, as the target remained the same, and therefore the daily average was always the same number also.

Now however, I need to add functionality that allows for promotions and therefore a different target for a particular individual from a particular date. As such, the average target hours per day would need to capture this. And then to further confuse matters, occasionally the target hours for each position will change from a particular date. And so again, this will need to be captured within the average target hours per day.

I've put together some dummy data. There's a fair bit more to the actual report, but if it I can just get my head around a workable solution for this, I should be able to incorporate this. Does anyone have any bright ideas?

Thanks in advance to any brainboxes out there!

Skins

Selecting attachment 828129 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
It may be more helpful to upload an .xlsx file.