New formula in field

[jkazan]

Hi

I need formula which calculate days after one task to the next task
I'd like to put that formula in column. Could you somebody help me?

Example:
milestone - date of delivery product A

task 1 / start date / finish date / number of days between deliver
A and start task 1 /number of days between finish date task 1 and star
date task 2

task 2 / start date / finish date / number of days between finis
date task 1 and start date task 2 /number of days between finish dat
task 2 and start date task 3

task 3 / start date / finish date / number of days between finis
date task 2 and start date task 3 /number of days between finish dat
task 3 and start date task 4

task 4 .......................etc

Could you somebody help me ?

Best regards
Jare

 

[Rob Schneider]

Can you please reframe the question? Perhaps also explain what you are
trying to do as there may be a built-in way in Project to do what you seek?

(are you entering the start/finish dates manually for each of these
tasks, or is Project computing them for you?)

--rms

www.rmschneider.com

 

[jkazan]

When i'm on gantt view by selected machine I'd like to see the numer o
days to next task so I need that formula. could you tell me how to d
it?

I have more than 3000 tasks in my projecy and it will be helper for
me.

I don't put start/finish data manually. But sometimes i moved it t
have no collision

 

[Rob Schneider]

You'll probably need to write some VBA code which you run to compute a
task number field called, say, "Days to Next Task". The algorithm would
be something like this: Loop through all tasks and for each task find
the "next" task. I presume that the "next" task will be the successor
task(s). Since there could be more than one successor task, figure out
which is the "next" task. This may not be trivial unless there is always
only one successor, which depends on how you set up your schedule. Then
use the built-in date functions, e.g. DateDiff() or ProjDateDiff(), to
find the date difference you are seeking. Put the result of that
computation in the custom field you defined [Days to Next Task].
Display that custom field on the table view connected to your Gant chart.

Out of curiousity... what do you do with this info? Looking at this for
3000 tasks seems like a big job.

--rms

www.rmschneider.com

 

[jkazan]

There is no simpler way? I have no idea about VBA code

 

[Rob Schneider]

If not done by VBA, e.g. the computer, you can do it manually.

A simpler way may be to re-think why you need this info to ensure you
really need it. I suppose it depends on the nature of the project as to
the value of having this detailed info on 3,000 tasks; I'm still
struggling to understand the purpose.

A simpler way would be to get back into the Tao of Project and use
Project as intended which will then allow you to use built-in
capabilities of Project without resorting to so much automated details.

--rms

www.rmschneider.com

 

[JulieS]

Hi Jarek,

You could export the information out of Project into Excel and do
the calculations there. However, short of VBA, there is no
automatic method in Project to calculate the difference between
dates on two separate tasks.

You can calculate the difference between dates on the same task
line, so I suppose in theory you could copy/paste the date of one
task into Start1 or Finish1 of the other task and then using a spare
Duration field (Duration1) calculate the difference. The downside
would be having to manually update the dates if things change.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[jkazan]

Rob - what do you mean: TAO of project?
As i wrote sometimes i manually moved start date of task - and whe
i'll see the number of free days before and after task it will be easie
for me.


Julies
I understand your idea. But it's many work to copy/paste start1 an
finish1 data. I have more than 3thausends tasks in my project.

There is no easier way?

 

[Rob Schneider]

I mean: use Project like Project wants to be used. My hunch is that you
probably could benefit by taking a look a the the basics, e.g. the
downside of manually setting and moving start/finish dates. You also
could benefit by taking a look at VBA macros. All this covered in any of
the good books about Project.

--rms

www.rmschneider.com

 

[JulieS]

Sorry, no. Other than using VBA to copy the information for you, or
exporting to excel and then writing the formula and copy/paste the
formula to the column.

Julie

 

[Steve House]

What determines so9 far the Start and Finish dates of the tasks in your
schedule? As I understand what you are asking, you have something like...

Task 1 / 14 Sep / 16 Sep
Task 2 / 22 Sep / 25 Sep
Task 3 / 01 Oct / 05 Oct
Task 4 / 08 Oct / 13 Oct


and you want a formula that will compute that there are 3 working days
between the end of Task 1 and the start of Task 2, 3 working day between the
end of Task 2 and the start of Task 3, 2 working days between the end of
Task 3 and the start of Task 4, etc. But we have to ask, how did the
schedule come to reflect those start and end dates in the first place? Did
you type the dates in or otherwise set them yourself or is Project
calculating those dates for you? And if you are manually determining the
start and finish dates, why are you intentionally delaying the starts at
all - why not start Task 2 as soon as Task 1 finishes, Task 3 as soon as
Task 2 finishes, and so forth? Why is there any gap at all?

The normal way to schedule is to set the start date of the Project to 14 Sep
and enter the tasks and their length: Task 1 3 days, Task 2 4 days, Task 3 3
days, Task 4 4 days. You then sequence the tasks by entering a series of
predecessor / successor relationships linking Task 1 -> Task 2 -> Task 3 ->
Task 4. This makes Project calculate the start and end dates of the tasks
with no delay between them, ie:

Task 1 / 14 Sep / 16 Sep
Task 2 / 17 Sep / 22 Sep
Task 3 / 23 Sep / 25 Zep
Task 4 / 28 Sep / 01 Oct

Then if you WANT there to be a delay between the end of Task 1 and the start
of Task 2, for example, you add that time into the link between them as a
LAG time entry so that the relationship becomes FS + 3 days, delaying the
start of 2 for 3 working days after the earliest date it COULD have started
given the finish date of Task 1.

Without resorting to Visual Basic programming the formula you are seeking is
impossible. The reason is that Project can only look at the data from ONE
task at a time to use in any user-defined formulas. If you want to see the
time between the finish of Task 1 and the start of Task 2, it would have to
read data from both Task 1 and Task 2 to accomplish it. But Project can't
do that as it sits. It can look at data for Task 1 and use it to compute
something about Task 1, it can look at data for Task 2 and calculate
something for Task 2, but it can't look at data from BOTH Task 1 and Task 2
at the same time. In the case of the formula you want, to compute the
numbers you want to see in, say, Task 2's line of the schedule, it would
need to read four pieces of information, the Finish of Task 1, the Start of
Task 2, the Finish of Task 2, and the Start of Task 3, and I'm afraid it
just can't do that.

 

[jkazan]

Steve,
I use normal way to schedule: set the start date of the Project ente
the tasks and their length. But I'm not always do autmoatcally resourc
leveling so it will be helpful for me.

Julies,
maybe other way.
Maybe it's possible to put finish data and start data next task
I mean:


task 1: st1, fi1,
task 2: st2, fi2, fi1, st3
task 3: st3, fi3, fi2, st4
task 4: st4, fi4,

Regards
J

 

[Steve House]

Sorry, but what you are trying to do in your example below can't be done
AFAIK. All of the data on the line for task 2, for example, must come from
the data stored about task 2 or from certain system level fields like the
current date. Data stored about other tasks, such as what you've listed as
"fi1" and "st3" in your example, is not available for display or calculation
in task 2's line in the table. A workaround would be to add a couple of
user-defined Date fields to the table and then use Paste Link to connect to
the data, similar to the way a hammock task is set up, but that is going to
be a manual process of Copy and Paste Link for each and every task.

You keep saying you need this because you don't use automatic leveling but
what's the connection with between your desire for these dates to be
displayed and leveling at all? How exactly are you going about adjusting
tasks to correct overallocations? It sounds like you might be manually
moving the task dates around - is that what you're doing?

 

[JulieS]

The only way to get task 1's finish and task 3's start into the
fields on Task 2, would be either copy/paste, copy/paste link (I
wouldn't recommend), or manually typing.

Again, the best option would likely be VBA if you have any skills in
that area or know of someone from within your organization who can
assist you to develop that code. Even then, you'd need to re-run
the code each time a change is made to refresh the data from the
other tasks.

Julie

 

[jkazan]

Steve,
these dates will be helpfull for me bec on resources view I can see how
much I can move tasks. It's my idea to makes easier my live at the
begining......later I hope I will have new ideas.

Yes, I manually moving the task dates. I'm new one in my
company.....but since 2 years or maybe more the resource leveling is
doing manually. I'm going to change it....but it's no so easy for new
person which has got first contact with MS PROJECT one month ago.
We have many changes in our schedule always but I hope that is not
reason to give automatically resource leveling resour up.

Julies,
I have no idea about VBA code at that moment. Is that complicated to
write VBA code for these dates

 

[JulieS]

Hi,
VBA is a programming language, so it does have a learning curve.
However, if you are motivated, it is entirely learnable within a
reasonable period of time. If you are looking for an exceptional
reference on VBA in Project, see Rod Gill's book:

http://www.projectvbabook.com

Julie

 

[Rob Schneider]

Jkazen

I gave you in my reply on 13 Sep the algorithm to use (which is as I
understood *what* you wanted to do). As Julie indicated, you can by Rod
Gill's book on VBA to learn how to do VBA. Well worth your time to
learn how to do VBA as it can save you an immense amount of time in your
life.

That being said, I still don't quite understand *why* you need this
information about successor tasks, and I can say with certainty that
when you hard-code dates to Project tasks (by moving them), it will
defeat the purpose of using Project and will in fact make it a whole lot
more complicated, and probably less useful. So just beware!

Enjoy.

--rms

www.rmschneider.com

 

[Steve House]

To reiterate Rob's statements ... moving tasks manually to adjust their
dates IS inputting setting their dates, specifically setting a Start No
Earlier Than constraint upon the task. This is rarely justified and almost
always leads to severe scheduling problems down the road. Start No Earlier
Than means just what it says ... under no circumstances will Project
schedule the task earlier than the constraint date, even if everything else
in the schedule - predecessor completion, resource availability, etc - says
it can and should start earlier. Let's say you shifted the task to start 15
OCt because the resource was busy on something else before that date. Now
things change and you find he's free so you update his calendar ... Project
SHOULD move the task back to start earlier because he's now available but it
can't because you have put a constraint on it that mandates that nothing
that happens in the schedule should ever, under any circumstances, allow
start the task before 15 Oct.

You may think you're making your life easier by doing it this way but
believe us, you're opening up a can of worms than can easily lead you into a
scheduling nightmare and projects that fail.