Project days allocation (better mail format)

[dumbaswood]

i have a project that is 32 workdays and i want to spread the 32 work days by
month until the 32 days are used up. my project starts on random dates and
end on random deate and can cross multiple months. my fiscal months are not
calendar months. i have calculated how many work days there are in my fiscal
months so i want a formula that will "bounce" against the available days i
have to allocate. So, below i am looking for a formula that will calculate 2,
20 and 12 = 32


Jan Feb Mar Apr May June
1/7-2/3 2/4-3/2 3/3-4/6 4/7-5/4 5/5-6/1 6/2-7/6 fiscal
20 20 25 20 20 25 workdays

--------------------------------------------------------------
proj start proj stop project duration

5/1/08 6/15/08 32 work days

Needed answer to be:

0 0 0 2 20 12 0

 

[Tushar Mehta (Microsoft MVP Excel 2000-2008)]

Not that replying to this message will necessarily lead to a solution
but...

How does 32 days get allocated as 2+20+12? That's 34 days.

Further, how can you have a random start date, a random end date and
have a predetermined duration?

Finally, to get the actual allocations, you have to know which days
are the workdays. For example, how did you know that there were 2
working days between 5/1 and 5/4 (the part you allocated to April)?

 

[dumbaswood]

Tushar, I am not sure if i should reply to you or not but i am in quite a
need for info here.......

1. ok, very minor error in email it should be 2+20+10 =32 that i am looking
for.

2. Try to imagine that i have a list of 100 projects. Every project will hve
a given/random start and stop date depending on each project. every
additional project has it own random/assigned start and stop date.

3. I know how many work days there are available in first month by using
Networkdays function in excel. I can EASILY find the available workdays in
the first month by using this function and calculating the number of
Networkdays between the project start date and the end of the fiscal month.

What i need to do is allocate all the 32 days to the available workdays in
each month until i spend/allocate all the 32 days of the project.

 

[Tushar Mehta (Microsoft MVP Excel 2000-2008)]

Suppose you reorganize your heaers along the lines of, say, in D2:I5:

Jan Feb Mar Apr May June
7-Jan 4-Feb 3-Mar 7-Apr 5-May 2-Jun
3-Feb 2-Mar 6-Apr 4-May 1-Jun 6-Jul
20 20 25 20 20 25

Also suppose you have the project start date in A8 and the duration in
B8.

Then, in D8 enter the formula
=MAX(0,MIN($B8-SUM($C8:C8),NETWORKDAYS($A8,D$4)-SUM($C8:C8)))

The above formula tested very lightly.