Project days allocation (better mail format)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dumbaswood - 05 May 2008 13:57 GMT

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

Reply to this Message

Tushar Mehta (Microsoft MVP Excel 2000-2008) - 05 May 2008 14:17 GMT

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)?

>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
[quoted text clipped - 17 lines]
>
> 0 0 0 2 20 12 0

Reply to this Message

dumbaswood - 05 May 2008 14:46 GMT

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-2" wrote:

> Not that replying to this message will necessarily lead to a solution
> but...
[quoted text clipped - 29 lines]
> >
> > 0 0 0 2 20 12 0

Reply to this Message

Tushar Mehta (Microsoft MVP Excel 2000-2008) - 05 May 2008 16:06 GMT

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.

> Tushar, I am not sure if i should reply to you or not but i am in quite a
>need for info here.......
[quoted text clipped - 49 lines]
>> >
>> > 0 0 0 2 20 12 0

Reply to this Message