Calculated Fields?
In the Open Standard
Bo JohnsenMember for 20 years 7 months
- Created
- 10
- 4
0
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
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
10 replies
Oldest first- 0Alexandre Faulx-Briole ·Bo,
How did you write the formula for Duration 3? the logic is Finish 3 - Start 3, right?
Therefore the formula for Start 3 is :
Finish 3 - (Finish 3 - Start 3),
a circular reference.
MS Project sends a warning about that, I understand by reading your post that MS Project 2000 does not and calculates an error.
Try another formula that does not generate a circular reference; I am not good enough at maths to tell you now what it should be.
All the best,
Alexandre - 0Bo Johnsen ·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
- 0Alexandre Faulx-Briole ·Bo,
1. looks like a bug, I get the same result with MSP 2003; I tried changing the project and tasks calendar to 24h/day, 7d/week, it’s even worse: Start 3 is somewhere 15 years ago
2. change your formula to [Finish 3] - [Number 3] and manage your project in days
Alexandre
P.S. your French is much better than my Danish; the closest I can say is: Mit freundlischen Grüssen - 0Bo Johnsen ·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. - 0James Griffiths ·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. - 0Bo Johnsen ·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 - 0Rodel Marasigan ·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
Log in or join to reply
ProjectControls.com is free to join. Members can post questions, reply, vote and follow discussions.
Existing members: your original username and password still work. Forgot password?