Hi Chandoo
I have designed a project management tool in Excel that details a project list in one tab (assign resource, estimated start date, estimated end date, allocated hours) and an out of hours list in another tab.
What I need to do is automatically calculate the estimated finish date for each project in the list to include the following process:
- Work out the number of working days the allocated hours equates to (**DONE using simple formula)
- Work our the estimated finish date excluding vacation time for that employee(**DONE using the WORKDAY formula to calculate based purely on the allocated hours/days)
- I then need to work out the number of hours the resource has logged as being out of office/on vacation that falls WITHIN the estimated start and finish dates I have calculated above (**I AM TRYING TO FIGURE THIS OUT!)
- With this information, I then create a second estimated finish date that a) takes into account the allocated number of hours needed for the project, and b) the number of hours the particular employee has logged as being out of office
At the moment, I've created a SUMPRODUCT formula that sometimes works out the hours out of office, but generally leaves the #hours out of office as blank, even when the project start/finish falls WITHIN time out of office.
I am using the below formula. (row three is an example project I'm looking at, column D is the start, and column G is the first estimated finish, the others named 'OOO' ranges refer to the list of hours logged as out of office)
Note - in this example, I'm not working out which employee the project has been assigned to for aim of make it simpler to use in this posting.
Please, please, please help me. I am really stuck as to what to do next...
Thanks
Jo
I have designed a project management tool in Excel that details a project list in one tab (assign resource, estimated start date, estimated end date, allocated hours) and an out of hours list in another tab.
What I need to do is automatically calculate the estimated finish date for each project in the list to include the following process:
- Work out the number of working days the allocated hours equates to (**DONE using simple formula)
- Work our the estimated finish date excluding vacation time for that employee(**DONE using the WORKDAY formula to calculate based purely on the allocated hours/days)
- I then need to work out the number of hours the resource has logged as being out of office/on vacation that falls WITHIN the estimated start and finish dates I have calculated above (**I AM TRYING TO FIGURE THIS OUT!)
- With this information, I then create a second estimated finish date that a) takes into account the allocated number of hours needed for the project, and b) the number of hours the particular employee has logged as being out of office
At the moment, I've created a SUMPRODUCT formula that sometimes works out the hours out of office, but generally leaves the #hours out of office as blank, even when the project start/finish falls WITHIN time out of office.
I am using the below formula. (row three is an example project I'm looking at, column D is the start, and column G is the first estimated finish, the others named 'OOO' ranges refer to the list of hours logged as out of office)
Code:
=SUMPRODUCT((OOOStart>=$D3)*(OOOFinish<=$G3)*($F$10:$F$12))Note - in this example, I'm not working out which employee the project has been assigned to for aim of make it simpler to use in this posting.
Please, please, please help me. I am really stuck as to what to do next...
Thanks
Jo
