Formula with Date custom field

[Sebastian Cordoba]

Hello everyone.

One of the Managers of my company doesn´t want the project dates to change
when resources report their assignments, BUT he also needs an indicator which
shows the difference between the actual finish date and the baseline finish
date, so we can tell if the tasks AND the Project were finished on time,
before or after expected.

In order to achieve that, I created a Date custom Field called "Actual
Finish Date" (at task and project level). The idea is that resources report
the actual finish date using it (I included this custom field in "My
assignments" view) without affecting the "real" Actual Finish field of their
assignments. I also created a number custom field with the following formula
(and the respective graphic indicators):

IIf([Baseline Start]=projdatevalue("NA"),-999,IIf([% Work
Complete]<>100,-555,IIf([Actual Finish
Date]=projdatevalue("NA"),-555,([Actual Finish Date]-[Baseline Finish])/480)))

It works perfectly at the task level, but doesn't do it at the project
level: If I insert the column "Actual finish Date" (Project level) in Project
Professional and add a value for it, some kind loop begins and doesn't stop.
I can tell because the gantt starts to "blink" and the system gets slow.

Do you know whats going on?

By the way: If I erase the references to this field in the formula, this
doesn´t happen.

 

[Rod Gill]

???!

If the dates don't change, then you lose much of the remaining value in the
schedule because how can you tell accurately what has to be done next week?
To do so you manually have to scan all Tasks in the past for incomplete
work, manually prioritize based on links and importance then manually level
the resource needs!@#!

I think you need to give a short lesson in scheduling to your manager or,
create a duplicate schedule with the word fiction added to the end of the
file name for your "management" report.

Remind the manager that the Baseline dates show the original dates and if
you print the Tracking Gantt chart then they can see where you are now
compared to original dates. You could create a View that only shows Baseline
bars and dates!


--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

Hello everyone.

One of the Managers of my company doesn´t want the project dates to change
when resources report their assignments, BUT he also needs an indicator
which
shows the difference between the actual finish date and the baseline
finish
date, so we can tell if the tasks AND the Project were finished on time,
before or after expected.

In order to achieve that, I created a Date custom Field called "Actual
Finish Date" (at task and project level). The idea is that resources
report
the actual finish date using it (I included this custom field in "My
assignments" view) without affecting the "real" Actual Finish field of
their
assignments. I also created a number custom field with the following
formula
(and the respective graphic indicators):

IIf([Baseline Start]=projdatevalue("NA"),-999,IIf([% Work
Complete]<>100,-555,IIf([Actual Finish
Date]=projdatevalue("NA"),-555,([Actual Finish Date]-[Baseline
Finish])/480)))

It works perfectly at the task level, but doesn't do it at the project
level: If I insert the column "Actual finish Date" (Project level) in
Project
Professional and add a value for it, some kind loop begins and doesn't
stop.
I can tell because the gantt starts to "blink" and the system gets slow.

Do you know whats going on?

By the way: If I erase the references to this field in the formula, this
doesn´t happen.

__________ Information from ESET Smart Security, version of virus
signature database 4574 (20091104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4574 (20091104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jonathan Sofer [MVP]]

Rod is right.

But from a purely technical perspective I wonder what would happen if you
set your custom formula field to "Use Formula" at the summary/grouping level
and then for the schedule in question under Tools>Options, enable the "Show
project summary task" checkbox.

Does task level 0 (the project level) now show the information you expect to
see?

Jonathan

???!

If the dates don't change, then you lose much of the remaining value in
the schedule because how can you tell accurately what has to be done next
week? To do so you manually have to scan all Tasks in the past for
incomplete work, manually prioritize based on links and importance then
manually level the resource needs!@#!

I think you need to give a short lesson in scheduling to your manager or,
create a duplicate schedule with the word fiction added to the end of the
file name for your "management" report.

Remind the manager that the Baseline dates show the original dates and if
you print the Tracking Gantt chart then they can see where you are now
compared to original dates. You could create a View that only shows
Baseline bars and dates!


--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

Hello everyone.

One of the Managers of my company doesn´t want the project dates to
change
when resources report their assignments, BUT he also needs an indicator
which
shows the difference between the actual finish date and the baseline
finish
date, so we can tell if the tasks AND the Project were finished on time,
before or after expected.

In order to achieve that, I created a Date custom Field called "Actual
Finish Date" (at task and project level). The idea is that resources
report
the actual finish date using it (I included this custom field in "My
assignments" view) without affecting the "real" Actual Finish field of
their
assignments. I also created a number custom field with the following
formula
(and the respective graphic indicators):

IIf([Baseline Start]=projdatevalue("NA"),-999,IIf([% Work
Complete]<>100,-555,IIf([Actual Finish
Date]=projdatevalue("NA"),-555,([Actual Finish Date]-[Baseline
Finish])/480)))

It works perfectly at the task level, but doesn't do it at the project
level: If I insert the column "Actual finish Date" (Project level) in
Project
Professional and add a value for it, some kind loop begins and doesn't
stop.
I can tell because the gantt starts to "blink" and the system gets slow.

Do you know whats going on?

By the way: If I erase the references to this field in the formula, this
doesn´t happen.

__________ Information from ESET Smart Security, version of virus
signature database 4574 (20091104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4574 (20091104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Marc Soester [MVP]]

Hi Sebastian,

This may be a silly question, but why dont you just compare what the
baseline finish was and once the project task is actually completed, let the
resource update the actual finish. This is as Project was anticipated to
work.

Because of the baseline you still have the original estimate, which is the
general idea of the baseline and you can compare it with the Finish Variance.
That will allow you to compare what was planned and what actually happened.

Wouldnt that achieve the same outcome?

 

[Sebastian Cordoba]

Hi Marc.

I agree with you, in fact, that was my first approach when I developed this
indicator: I was using the finish variance, as simple as that. BUT the
problem is: if resources report the 'Actual Finish' for their assignments,
dates of the succesors tasks may change, and that's what the manager wants to
avoid. He doesn't want this dates to change, specially because work is
distributed in a very specific way (day by day, hour by hour, for every
resource). That was the reason why I created this alternate Date custom field
(Actual Finish Date) that doesn't affect the initial work distribution.

I as told you before, it works fine at the task level, not at Project level.
When I modify the value of "Actual Finish Date" field (in Project
Professional), some kind of loop or process in the background is executed. It
has something to do with the formula, but I don't know what is exactly.

 

[Sebastian Cordoba]

Thank you both for your reply.

Hi Rod: I get what you mean and agree with you, but there are some cultural
factors in my enterprise I have to deal with. Let's say that, in this moment,
my priority is meeting the Manager's requirements.

Hi Jonathan: It works fine. The Project summary task shows the expected
value. But I need to create a Project Center view which should include the
indicator for every project, that's why I need this field to be created at
project level. Is there a formula to copy it from the summary task? (Not
involving macros)

Thanks!

 

[Jonathan Sofer [MVP]]

Sebastian,

Once you set the custom task field to use "formula" for summary rows and it
works correctly and shows the desired value at task 0 (project level task),
you can then create a custom project field of the same type and the formula
would simply be equal to the name of your custom task field.

So if you custom task field is a date field called "Task Actual Finish
Date", you would create a custom project date field called something like
"Project Actual Finish Date" and the formula would simply be "=[Task Actual
Finish Date]"

Hope this helps,

Jonathan

 

[Sebastian Cordoba]

Hi Jonathan.

It works perfectly. Thank you!

 

[Jonathan Sofer [MVP]]

I'm glad it worked and thanks for letting us know.

Jonathan