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.

Calculated Fields?

10 replies [Last post]
Tue, 2007-03-06 06:03
Bo Johnsen
User offline. Last seen 8 years 9 weeks ago. Offline
Joined: 28 Feb 2006
Posts: 119
Groups: Building Information Modelling, Civil Engineering
Dear all,

I’m trying to do a Procurement Schedule calculated by customized/calculated fields in MS Project (2000). Based on a milestone value called “Material Required on Site”, I want to have e.g. a duration called “Lead In Time”, one named “Approval of Design by Employer” and last one named “Design” and then show all these 3 durations (in different colors) + plus the milestone on the same line in the bar chart. All this is back-calculated from the milestone value (“Required on Site”).

Definition of the milestone is given by the normal “Start/Finish”-value and the rest is defined by “Duration/Start/ Finish 1”, “Duration/Start/ Finish 2", and “Duration/Start/ Finish 3”-values and then the same values defined under “Bar Styles”.

No problem getting the desired result on the bar chart when I manually enter the misc. start/finish values, however, when using calculated fields it starts to do funny things.

“Finish 3” is defined as “Finish-1” (day before) which gives correct result, but “Start 3” is defined as “Finish 3” minus “Duration 3” and here the fun starts. If “Duration 3” is 1 week, then “Start 3” should be one week before “Finish 3”, however when writing my formula as [Finish 3] – [Duration 3] is gives me a date that is located some 8 years, 2 months and x days before.

How do I write my formula correct for the calculated field of “Start 3” so the result will be a week before “Finish 3” based on the “Duration 3”-value?

Thanks,


Bo
Top

Replies

Sun, 2007-04-01 22:56
#1
Zhang Haixiang
User offline. Last seen 3 years 29 weeks ago. Offline
Joined: 14 Apr 2005
Posts: 250
Groups: None
use this method, you can deal with different calender for different resource
Top
Sun, 2007-04-01 22:54
#2
Zhang Haixiang
User offline. Last seen 3 years 29 weeks ago. Offline
Joined: 14 Apr 2005
Posts: 250
Groups: None
Try to use start3=projsub([finish3]-[duration3],"calendar")

where calendar is the name of calender you want to use
Top
Wed, 2007-03-07 09:32
#3
Bo Johnsen
User offline. Last seen 8 years 9 weeks ago. Offline
Joined: 28 Feb 2006
Posts: 119
Groups: Building Information Modelling, Civil Engineering
Rodel,

Thanks for the solution. Now it makes sense: Number or Duration in customized fields, just a factor 480 (for an 8hrs day).


Regards,

Bo
Top
Wed, 2007-03-07 09:04
#4
Rodel Marasigan
User offline. Last seen 12 hours 16 min ago. Offline
Joined: 25 Oct 2006
Posts: 1699
Groups: Petro-Chem / Oil & Gas
Hi,

Duration on Customize Field is storing total minute value of your hours defined in Option Calendar tab. (Hour per day) Ex: 1d = 8 hrs x 60 min = 480mins. Therefore if you use it for customize/calculated field the correct formula to your example is “Start 3” = “ [Finish 3] – ([Duration 3]/ 480)”.

Your comment: If "Finish 3" is 22-06-07 and "Duration 3" is 7 days, I should get "Start 3" to be 15-06-07 for the formula [Finish 3] - [Duration 3], however, I get some date in April 1998.
To interpret your formula:
“Duration3” = 7d = (8hrs x 60 x 7 = 3360 min)
“Finish3” = 22-06-07
“ Start3” = 22-06-07 - 3360 = 10-04-1998

If you use Number instead of Duration then it will interpret value as day. Hope this justify to your duration problem.

Regards,
Rodel
Top
Tue, 2007-03-06 10:19
#5
James Griffiths
User offline. Last seen 15 years 16 weeks ago. Offline
Joined: 19 May 2006
Posts: 435
Groups: None
Bo,

Just a thought: Using Start 1, Finish 1, Start 2, Duration 2 etc. retain no links with START, DURATION and FINISH. There is no intelligence that underpins them, whereby if you amend a DURATION on one activity it will amend a Start 1, Finish 1 date on another activity. Whether inputting a formula introduces any level of "intelligence", I’m not sure - because I’ve never actually tried doing it.

I’m eager to see what results you guys arrive at.

Cheers.

James.
Top
Tue, 2007-03-06 09:36
#6
Bo Johnsen
User offline. Last seen 8 years 9 weeks ago. Offline
Joined: 28 Feb 2006
Posts: 119
Groups: Building Information Modelling, Civil Engineering
Alexandre,

That works perfect with "Number"!

Thank you.

Bo


P.S. "Mit freundlischen Grüssen" is German and not even close to Danish (no offence taken). Have to admit I cheated a bit myself: Took an e-mail from one of our French guys in the company and copied the Salutations-part.
Top
Tue, 2007-03-06 08:29
#7
Bo Johnsen
User offline. Last seen 8 years 9 weeks ago. Offline
Joined: 28 Feb 2006
Posts: 119
Groups: Building Information Modelling, Civil Engineering
Hi Alexandre,

Thanks for your answer.

No, I don’t get any error message about circular reference.

I probably forgot to mention that all my duration values are entered manually, i.e. known in advance, and only start and finish values are back-calculated based on the milestone for when misc. materials are required on site and the duration for lead in, approval and design.

If "Finish 3" is 22-06-07 and "Duration 3" is 7 days, I should get "Start 3" to be 15-06-07 for the formula [Finish 3] - [Duration 3], however, I get some date in April 1998. If I enter "7" in the formula instead of [Duration 3] I get the right answer, i.e. 15-06-07, but I wanted it to be done automatically. It’s like I’m missing some kind on function in front of [Duration 3] enabling MS Project to carry out the calculation correct, but which?

I know one could just write the seperate lines/bars for lead in, design, etc. and then just format correctly and tick on "Always roll up Gantt Bars" under "Layout". This would give the same visual appearance, but I wanted to work with calculated fields instead, thereby using one line instead of several lines rolled up.

Sincères salutations,

Bo
Top
Tue, 2007-03-06 10:49
#8
Bo Johnsen
User offline. Last seen 8 years 9 weeks ago. Offline
Joined: 28 Feb 2006
Posts: 119
Groups: Building Information Modelling, Civil Engineering
James,

Correct that custom start/finish/duration retain no links with START, DURATION & FINISH and I’m sure you are right when stating that about how one activity will amend - or actually won’t - another activity when using formula. I haven’t tried that either and I don’t see how it would be possible by only using calculated fields and no macros.

However, I’m doing all these calculations of misc. custom start/finish dates in one line, i.e. for one activity, at the time. Thereby, using formulas the custom start/finish values will be amended within that one line. Basically, it is just one spreadsheet with many columns for misc. start/finsh dates each calculated from the previous based on a custom duration. Can be done in EXCEL w/o problems, but as Alexandre states, there must be a bug in MS Project (unless we doing something wrong, which could be the case).

It works when doing the formula with "Number x" instead of "Duration x" as Alexandre suggested, so I’ll continue along that line.

Thanks,

Bo
Top