D
dumbaswood
I want to allocate $1000 by month based on the available days in each month
depending on when a project starts based until that the project is complete.
If i need more days beyond the first month, i want excel to move on to the
next month and so on and use whatever available days are necessary and
calculate the $ allocation of the project. my sheet looks like this:
First 5 columns of the spreadsheet:
Project Project Start
Amount Start Finish QTR Workdays $/Day
$1,000 1/10/08 4/10/08 QTR 2 66 $15
Note: I wrote a formula to calculate the start qtr based on my fiscal
calendar. Also, the workdays are auto calculated using "Networkdays" based on
project start and project finish. Also, the $/Day is a simple automatic
division the the Amount and the Workdays.
I want to allocate the $15 a day x the number of days available to work in
the month and place it in the monthly cell. So, i have to allocate 66 days
of work at $15 per day. My fiscal months vary and do not track to Gregorian
calendar months.
The next 5 columns of the spreadsheet:
Month Jan Feb Mar Apr
Start 1/7/08 2/4/08 3/3/08 4/7/08
Stop 2/3/08 3/2/08 4/6/08 5/4/08
Networkdays 20 20 25 20
Note: The network days are automatically calculated using Networkdays formula.
Because of my fiscal month situation, January in this case has only 17 work
days available to me because of when this specific project starts. So, i
want to multiply the $19 x 17 days and automatically put that $ amount in
Jan. In Feb I have 20 days available and need all of them so i want to
multiply $19 x 20. Now i have used 37 days so i need all of Marchs' 25 days
and want $15 x 25 in the mar cell. Now I only need 4 days to use up all the
66 days of the project so i want to put $15 x 4 in Apr. Now i have
automatically allocate the entire project. I the idea is to allocate all the
$ using the available days in the month by using the total project days
available to me.
I want all the cells for all the months to be the same so i can copy them
down the spreadsheet and use the formula for multiple projects. In this
example may-dec should say $0. If my project should start in may then
jan-apr should return 0$.
Can you help me?
depending on when a project starts based until that the project is complete.
If i need more days beyond the first month, i want excel to move on to the
next month and so on and use whatever available days are necessary and
calculate the $ allocation of the project. my sheet looks like this:
First 5 columns of the spreadsheet:
Project Project Start
Amount Start Finish QTR Workdays $/Day
$1,000 1/10/08 4/10/08 QTR 2 66 $15
Note: I wrote a formula to calculate the start qtr based on my fiscal
calendar. Also, the workdays are auto calculated using "Networkdays" based on
project start and project finish. Also, the $/Day is a simple automatic
division the the Amount and the Workdays.
I want to allocate the $15 a day x the number of days available to work in
the month and place it in the monthly cell. So, i have to allocate 66 days
of work at $15 per day. My fiscal months vary and do not track to Gregorian
calendar months.
The next 5 columns of the spreadsheet:
Month Jan Feb Mar Apr
Start 1/7/08 2/4/08 3/3/08 4/7/08
Stop 2/3/08 3/2/08 4/6/08 5/4/08
Networkdays 20 20 25 20
Note: The network days are automatically calculated using Networkdays formula.
Because of my fiscal month situation, January in this case has only 17 work
days available to me because of when this specific project starts. So, i
want to multiply the $19 x 17 days and automatically put that $ amount in
Jan. In Feb I have 20 days available and need all of them so i want to
multiply $19 x 20. Now i have used 37 days so i need all of Marchs' 25 days
and want $15 x 25 in the mar cell. Now I only need 4 days to use up all the
66 days of the project so i want to put $15 x 4 in Apr. Now i have
automatically allocate the entire project. I the idea is to allocate all the
$ using the available days in the month by using the total project days
available to me.
I want all the cells for all the months to be the same so i can copy them
down the spreadsheet and use the formula for multiple projects. In this
example may-dec should say $0. If my project should start in may then
jan-apr should return 0$.
Can you help me?