Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Hours

5 replies [Last post]
Mon, 2010-04-12 10:36
Dan Mårtensson
User offline. Last seen 11 years 3 weeks ago. Offline
Joined: 4 Feb 2010
Posts: 59
Groups: Civil Engineering
Hi everyone,

I am in a need for a formula that can show me planned hours up to status date.

Does anyone have this typ of formula for custom fieldes? I have try excel but ut takes alot of time.

Thaks

Dan
Top

Replies

Tue, 2010-04-13 08:00
#1
Dan Mårtensson
User offline. Last seen 11 years 3 weeks ago. Offline
Joined: 4 Feb 2010
Posts: 59
Groups: Civil Engineering
I changed the formula a bit,

IIf([Status Date]>[Baseline Start];IIf([Status Date]>=[Baseline Finish];[Duration]/60;ProjDateDiff([Baseline Start];[Status Date])/60);0)

Now I can see the planned value up to status date

Thanks

Bo and Trevor

Regards

Dan
Top
Tue, 2010-04-13 06:59
#2
Dan Mårtensson
User offline. Last seen 11 years 3 weeks ago. Offline
Joined: 4 Feb 2010
Posts: 59
Groups: Civil Engineering
Hi Bo,

This is exactly what I am looking for! Thank you!

When I am working with a lot activities I think this is a nice way of getting a good look for each activity.

I am not saying that the other way is bad; I just think this is easier.

Regards

Dan
Top
Tue, 2010-04-13 03:48
#3
Bo Johnsen
User offline. Last seen 8 years 7 weeks ago. Offline
Joined: 28 Feb 2006
Posts: 119
Groups: Building Information Modelling, Civil Engineering
Trevor’s solution is actually not so time consuming. Have the "as planned" programme in a seperate programme. Adjust the Status Date, get the hours as Trevor suggest and then copy/paste the whole column over in one go (requires the same order of ID, but that is easily done).

Anyway, formula for a customized field could be something along the lines of:
IIf([Status Date]>[Start];IIf([Status Date]>=[Finish];[Duration]/60;ProjDateDiff([Start];[Status Date])/60);0)

Regards,

Bo
Top
Tue, 2010-04-13 02:23
#4
Dan Mårtensson
User offline. Last seen 11 years 3 weeks ago. Offline
Joined: 4 Feb 2010
Posts: 59
Groups: Civil Engineering
Hi Trevor,

This is a good way of doing it, but when the plan contains 2000 activities this way is very time-consuming.

What I wish for is that have a column that can show me this all the time for each task and summery.

Any more ides?
Top
Mon, 2010-04-12 18:50
#5
Trevor Rabey
User offline. Last seen 1 year 21 weeks ago. Offline
Joined: 29 Nov 2005
Posts: 530
Groups: None
Just get the "as planned" version of your plan, before any updates were made to it, set the status date, select the tasks that should (ie were planned to) have started by the status date, and click the "Update as scheduled" button on the tracking toolbar. Then see Actual Work in the Tracking Table.
Also, in MSP2003 you can use the Analysis Toolbar to export the Cumulative Work, same thing.
Top