How to get total work by resource by month

[qwe]

I have extracted a bunch of the task and resource fields into excel for
manipulation. What I cannot seem to figure out is how to get the monthly
effort by resource. I need to compare our "MAn-Month" estimates against our
actual "Man-Month" effort actuals.

ANy ideas on how to calc this or derive it it.

Thanks

 

[John]

I have extracted a bunch of the task and resource fields into excel for
manipulation. What I cannot seem to figure out is how to get the monthly
effort by resource. I need to compare our "MAn-Month" estimates against our
actual "Man-Month" effort actuals.

ANy ideas on how to calc this or derive it it.

Thanks

qwe,
Just how did you "extract" the fields into Project? If you used an
export map, what you have is the non-timephased data (e.g. total hours
for a resource assigned to a task versus the monthly hours). However
since you posted in the developers newsgroup I will assume you are using
VBA for the "extraction". It should then be a simple matter of using the
TimeScaleData Method to pull the data out in months.

One thought though. You mentioned comparing man-month estimates.
Depending on what your company's working calendar looks like, you may
need to tweak the timescale values so they represent equivalent
manmonths.

Hope this helps.
John
Project MVP

 

[SAFDF]

I am using the Timescaledata export routine originally poster on one of the
sites. I am using:
For Each Tsv In ActiveProject.ProjectSummaryTask.TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork, _
TimescaleUnit:=pjTimescaleMonths, _
Count:=1)

and get a month by month listing of each resource and the listing of each
task and effort.
Resource Name Task Name 7/1/2004 8/1/2004 9/1/200
--------------------------------------------------------------------------------------------
Dorantes; Rafael Project Start-Up 9 2 0
Dorantes; Rafael Plan for Workshop including core Resources 0.4 0 0
Dorantes; Rafael Project Plan 0.4 0 0
Dorantes; Rafael Risk Analysis Workbook 0.666666 0 0
Dorantes; Rafael Project Estimating & Planning Workbook 0.375 0.158 0
Dorantes; Rafael QA Plan 1 0.333333333 0

THe problem I'm trying to solve is can I get a man month equivilant total
by task? SO I would then get something like;

Task Name 7/1/2004 8/1/2004 9/1/2004
Project Start-Up 3 3 1
Risk Analysis 14 22
10

Is this possible to extract?

Jeff

Jeff

 

[John]

I am using the Timescaledata export routine originally poster on one of the
sites. I am using:
For Each Tsv In ActiveProject.ProjectSummaryTask.TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork, _
TimescaleUnit:=pjTimescaleMonths, _
Count:=1)

and get a month by month listing of each resource and the listing of each
task and effort.
Resource Name Task Name 7/1/2004 8/1/2004 9/1/2004
------------------------------------------------------------------------------
--------------
Dorantes; Rafael Project Start-Up 9 2 0
Dorantes; Rafael Plan for Workshop including core Resources 0.4 0 0
Dorantes; Rafael Project Plan 0.4 0 0
Dorantes; Rafael Risk Analysis Workbook 0.666666 0 0
Dorantes; Rafael Project Estimating & Planning Workbook 0.375 0.158 0
Dorantes; Rafael QA Plan 1 0.333333333 0

THe problem I'm trying to solve is can I get a man month equivilant total
by task? SO I would then get something like;

Task Name 7/1/2004 8/1/2004 9/1/2004
Project Start-Up 3 3 1
Risk Analysis 14 22
10

Is this possible to extract?

Jeff

Jeff,
Sure, no problem. With VBA you can get anything you want (just like
Alice's restaurant). I will assume you want to calculate equivalent
heads based on the project calendar as opposed to a generic 20 days per
month. The following code should give you what you need (if I understand
your need correctly).
Sub EquivHeads()
For Each t In ActiveProject.Tasks
If Not t Is Nothing And t.Summary = False Then
First = t.Start
Last = t.Finish
Yr = Year(t.Start)
mo = Month(t.Start)
Set taskhrs = t.TimeScaleData(First, Last, _
Type:=pjTaskTimescaledWork, _
timescaleunit:=pjTimescaleMonths)
For i = 1 To taskhrs.Count
GoSub WrkHrsMonth
EqHds = taskhrs(i).Value / 60 / 8 / WkDay
'[put the above value in Excel]
If mo = 12 Then
mo = 1
Yr = Yr + 1
Else
mo = mo + 1
End If
Next i
End If
Next t
Exit Sub
WrkHrsMonth:
WkDay = 0
Set ThisMo = ActiveProject.Calendar.Years(Yr).Months(mo)
For j = 1 To ThisMo.Days.Count
If ThisMo.Days(j).Working = True Then WkDay = WkDay + 1
Next j
Return
End Sub

I only gave it a quick test on a task that spans 2004/2005 so I won't
guarantee it's totally bug free but it should be pretty close. If I were
doing this for myself I would probably create an array for all working
day values in the project so they wouldn't need to be calculated over
and over for each task. But then, that's another story.

John
Project MVP

 

[ew]

John, I modofoed your code a bit, and got the results I'm looking for Thanks
a Ton!. I tried however to bring back the scheduled work(WORK) from the plan
but I keep getting the Actual Work. I have changed the PJ portion of the
equation thinking that I was changing the pointer to the other data. What
have I done wrong?

I am using the Timescaledata export routine originally poster on one of the
sites. I am using:
For Each Tsv In ActiveProject.ProjectSummaryTask.TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork, _
TimescaleUnit:=pjTimescaleMonths, _
Count:=1)

and get a month by month listing of each resource and the listing of each
task and effort.
Resource Name Task Name 7/1/2004 8/1/2004 9/1/2004
------------------------------------------------------------------------------
--------------
Dorantes; Rafael Project Start-Up 9 2 0
Dorantes; Rafael Plan for Workshop including core Resources 0.4 0 0
Dorantes; Rafael Project Plan 0.4 0 0
Dorantes; Rafael Risk Analysis Workbook 0.666666 0 0
Dorantes; Rafael Project Estimating & Planning Workbook 0.375 0.158 0
Dorantes; Rafael QA Plan 1 0.333333333 0

THe problem I'm trying to solve is can I get a man month equivilant total
by task? SO I would then get something like;

Task Name 7/1/2004 8/1/2004 9/1/2004
Project Start-Up 3 3 1
Risk Analysis 14 22
10

Is this possible to extract?

Jeff

Jeff,
Sure, no problem. With VBA you can get anything you want (just like
Alice's restaurant). I will assume you want to calculate equivalent
heads based on the project calendar as opposed to a generic 20 days per
month. The following code should give you what you need (if I understand
your need correctly).
Sub EquivHeads()
For Each t In ActiveProject.Tasks
If Not t Is Nothing And t.Summary = False Then
First = t.Start
Last = t.Finish
Yr = Year(t.Start)
mo = Month(t.Start)
Set taskhrs = t.TimeScaleData(First, Last, _
Type:=pjTaskTimescaledWork, _
timescaleunit:=pjTimescaleMonths)
For i = 1 To taskhrs.Count
GoSub WrkHrsMonth
EqHds = taskhrs(i).Value / 60 / 8 / WkDay
'[put the above value in Excel]
If mo = 12 Then
mo = 1
Yr = Yr + 1
Else
mo = mo + 1
End If
Next i
End If
Next t
Exit Sub
WrkHrsMonth:
WkDay = 0
Set ThisMo = ActiveProject.Calendar.Years(Yr).Months(mo)
For j = 1 To ThisMo.Days.Count
If ThisMo.Days(j).Working = True Then WkDay = WkDay + 1
Next j
Return
End Sub

I only gave it a quick test on a task that spans 2004/2005 so I won't
guarantee it's totally bug free but it should be pretty close. If I were
doing this for myself I would probably create an array for all working
day values in the project so they wouldn't need to be calculated over
and over for each task. But then, that's another story.

John
Project MVP

 

[John]

John, I modofoed your code a bit, and got the results I'm looking for Thanks
a Ton!. I tried however to bring back the scheduled work(WORK) from the plan
but I keep getting the Actual Work. I have changed the PJ portion of the
equation thinking that I was changing the pointer to the other data. What
have I done wrong?

Jeff,
First, what is modofoed? Is that a new transportation device similar to
the Segeway perhaps? Or maybe a new fast food from McDonalds.

I don't understand what you said you did to the code. As originally
written it already extracts scheduled Work. The formula and subroutine
convert it to equivalent heads.

Puzzled,
John