Pivot table: Distributing Units Between start and end date

[chullan88]

Hi,

I have a list of activities with start and end date and their assigned resources.
What I want is a pivot table showing the individual dates in the columns and the resources in the rows with their units.

For Eg. I have an activity A starting on Mar 3rd and ending on Mar 8th.So the duration is 6 days.for this activity there is a resource "Mason" with 200 units.

Assuming uniform distribution for the units over the duration,I want to obtain the value (200/6) in the row against dates Mar3-Mar8 in the columns.

i have attached a sample sheet.Please check.


NOTE : If I had an option to avoid weekends when distributing the resource units,it would be great.
( For eg in this case Mar 4,5 are holidays.So for Mar 3,6,7,8 the units will be (200/4)


Thanks!!!

[Dineth]

use MS project, instead excel....