Expand my Community achievements bar.

Help With Calculation Predicting Total Hours Spent - Different from EAC

Avatar

Level 4
Hi All, I am new to WF, currently in the middle of my integration, planning to go live in the next 5 weeks. I am struggling with a calculation that is critical to project health. What I am looking for is a calculation that adds the total number of hours that have submitted against a project in the timesheet (Actual Hours) plus the hours that are still assigned to a resource or role in the future. It is fine if this calculation can only occur on the project level (in fact it may be better in my case). Almost 100% of our projects are a fixed budget with a fixed amount of hours, using this calculation will allow me to determine lots of other metrics including: True % complete (dividing actual hours by this calculation) Actual Effective Bill Rate (dividing the $ Budget by this calculation) % over or under hours budget Etc. Ideally, the calculation would be: Actual Hours - From the start of the project through the end of the previous week PLUS Scheduled Hours - Current week until the end of the project I can live with it if the future scheduled hours need to use today as the start point. The EAC calculation comes close to doing this, but too much is dependant on the team actually updating the % complete on the task level, which I assume will not actually happen without lots of prodding. Kris Murphy Bluecadet
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

5 Replies

Avatar

Level 3

Kris,

If I am understanding this correctly you are looking for the hours remaining for the project. So Planned Hours for the project minus the Actual Hours for the project. I worded it differently but think it is the same thing. Is that correct? If that is the case then below text mode on a Project Report should give you what you are looking for.

displayname=Remaining Hours textmode=true valueexpression=CONCAT(DIV(SUB({workRequired},{actualWorkRequired}),60)," Hours") valueformat=HTML

Avatar

Level 4

@Adam Millet - inactive‚

Thanks for the reply. I'm not sure if that 100% gets me to what I am looking for since (I think) it is possible to have more hours projected at completion for a task than what is represented in the plan number.

For instance, there is a task spanning two months with 100 planned hours (evenly broken out for each month). At the end of the first month, the team member has entered 100 hours towards the task, but there are still 50 hours scheduled for the next month (the task is going over budget).

Using your math, I think it would tell me that there were 0 hours remaining to be worked since it would be subtracted actual hours from plan. But there are 50 hours next month that still need to be worked, so the total amount of time it would take to complete the task is 150 hours.

Does that make sense, is there a solve for this? We are not up and running on Workfront yet (still in integration), so still learning the ins and outs.

Thanks.

Avatar

Level 3

Kris,

I have been testing this the past few days and cannot find a way to accomplish this. The problem that I am running into to get this to work is there is not a way to break out the planned hours evenly through the month that would be consistent. However, there is a budgeting tool in Workfront that is already built that may help with this. Here is more info on this tool.

Avatar

Level 4

@Adam Millet - inactive‚

Thanks I will look at that. What is surprising to me is that there is no way to calculate the future scheduled hours a user. The information exists in the schedule since the hours can be contoured, but there is no way to calculate the amount of future hours that are assigned to a team member or role?

Avatar

Level 2

Hi Adam and Kris, I'm trying to figure out a similar problem. Essentially, I want to be able to compare my baseline against the Actual hours/Costs PLUS the future Planned Hours/Costs. Adam your code was very helpful, but it still assumes that a project will come in on plan. However that does not help me determine the profit/loss for a fixed revenue project, since increasing the plan also increases the calculated revenue.

The key here, as Kris said, is that more hours may be needed than the plan accomodates, or perhaps more actual hours were billed in the past than planned, and future planned hours cannot cover them.

I'm very new to workfront and textmode, so there is a good chance that I'm missing something.

But my thought was perhaps we can find a way to filter all planned hours beyond TODAY or this week. Is this possible through a report and some filtering? I haven't been able to figure out how to find the planned date for planned hours.

Then, as Kris originally asked, we could add actual hours to the future planned hours which may give a new total which might be higher than the total plan.

thanks for your patience with me- I'm new.