Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Project days allocation (better mail format)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Project days allocation (better mail format)
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)? On Mon, 5 May 2008 05:57:00 -0700, dumbaswood wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Project days allocation (better mail format)
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... 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)? On Mon, 5 May 2008 05:57:00 -0700, dumbaswood wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Project days allocation (better mail format)
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. On Mon, 5 May 2008 06:46:02 -0700, dumbaswood wrote: 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... 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)? On Mon, 5 May 2008 05:57:00 -0700, dumbaswood wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Project days allocation | Excel Worksheet Functions | |||
find the beginning and end days of a project | New Users to Excel | |||
Calculate number days + hours to complete a project | Excel Worksheet Functions | |||
How to format date cells, Excel template Project scorecard matrix | Excel Worksheet Functions | |||
Outlook 97 Mail Format | Excel Discussion (Misc queries) |