Column representing the # of days from the start of a specific tas

[Scrufnut]

Problem fixed. Thanks for all you help Jan!

Ed

Hi,

I'm not going to republish the how to do a formula either but maybe we can
get a little closer to a solution.
If you want to see the difference between the start of a task and another
date which exists in an other task, and which may move whenever there is a
plan change, the reply is simple: can't be done with a formula. You need a
VBA procedure to do that.
Formulas only handle data of the task itself, and data on Project level.
For instance, you might set the contract signing date manually as the
project start date - then a formula would be feasible. But should the date
of your signing task move, then you would have to adjust that start date of
the project: manually again.

If either of those solutions may interest you, tell me, I'll give a hand.
Hope this helps,

 

[Scrufnut]

Problem fixed. Thanks for all you help Jim!

Ed

I originally showed you projDateDiff, The syntax for regular DateDiff is:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

You can get a lot of help here (it will probably take you out of your
comfort zone):
Press ALT+F11 (Visual Basic editor opens)
Press F2 for the Object browser.

Search on the term DateDiff. Too much information to publish here.
Once it shows up, right click on it, and select help.

Remember, if your original "Contract Signed" is a task and that date
changes, then you have some much more serious programming to do (unless you
hard code the date into the formula). Best to use [Project Start]
--
If this post was helpful, please consider rating it.

Jim

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

Jim,

We are on the right track, thanks so much!

Yes, I was wanting to create a formula to be shown in a column.

I inserted your forumla in a custom duration field named "Duration1". I
received a syntax error and when I looked at the formula, it highlighted
where the software automatically inserted "TASK" prior to "calendar". I tried
pulling "TASK" and it erred again.

So I did as you recommended and dropped "calendar". The formula works
however the result is in # of work days.

Is there a way to achieve the result in "# of calendar days"?

Ed

I am not quite sure what you want ...
Do you want the result to be in days, such that "Contract Signed" is Day1
and "Contract Complete" is day 193?

You can format the time scale to give you a scale like Day1, Day2, Day3...
For something like that, the Help would be the best solution.

If you need to see that "Design Review" start on contract day "23" you can
do that with the formatted time scale as well.

It seems like you wish a formula so this can be shown in a column. If that
is the case, try inserting a custom duration field like Duration1. Assign a
forumula to Duration1 like this:

ProjDateDiff([Project Start],[Start], calendar)

This will calculate the number of work days between the Project Start Date
and the start date of the task on that line. You can hard key dates if
needed, but that is not recommended.

"Calendar" is an optional parameter of the forumula. It needs to be deleted
here if you are using the default. If you have a named calandar for this
project, you should assign it here.

See if that helps, I am sorry you were not treated professionally online.

--
If this post was helpful, please consider rating it.

Jim

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



:

I would like to add a column that shows the number of days between a specific
task (in this case -"contract signing") and the start of every other task.
How do I do this? Is there an existing field type that would let me do this?
Do I use a formula, and if so, where can I find basic instructions on how to
create one?

 

[John]

Problem fixed. Thanks for all you help John!

Ed

Ed,
You're welcome and thanks for the feedback. It was a fun thread.

John

Jack,

Well it may have sounded simple to you guys! LOL

I applied the following formula "([Start]-[Project Start])" and my
results
are mere fractions of days rather than the days they should be.

Did I miss something?

Ed,
My my, we're having quite a time with this. Remember how I said the
spare duration fields already convert values to days and that's why I
added the "480" factor to the formula I gave you for elapsed time? Well
the same thing applies to using Jack's suggestion (that's why I avoid
using spare duration fields for formulas). The formula you need is:

([Start]-[Project Start])*480

Try that.

John
Project MVP

:

John,

If you just want elapsed days you can simply subtract one date from
the
other instead of using datediff.
Dates are stored as serial numbers so you can do simple subtraction
and
round off.

-Jack Dahlgren

Jack,
Thanks. I forgot about the simple approach.

John
Project MVP

Thanks John! Actually Jim Aksel's response was almost identical
although
he
based his formula off the Project Start Date which is OK because
all
of
our
schedules that I can think of have a Project Start Date that
coincides
with a
"Contyract Signing Date". I will retain your info in case we ever
have a
situation that it does not.

I would like to know how to modify the formula to get the result
in
"elapsed
days".

Ed
Ed,
You're welcome. I figured there would be responses other than mine.

If you want the result in elapsed days, the formula has an extra
twist
in it, but try this:
DateDiff('d',[Date1],[Start])*480

Note: the 480 factor is needed to translate the "days" (i.e. 'd' in
the
formula) into days in the duration field because spare duration
fields
already apply a "correction" factor.

John
Project MVP

:

Your sarcastic response is NOT appreciated! FYI, I've been
using
the
HELP
feature along with MS Project 2003's 1000 page manual to find
a
resolution.
If it was that easy to find, I wouldn't be addressing this
community!

Scrufnut,
Obviously you are pretty frustrated. Project can do that to you.
Let's
see if I can help.

What you want could be done by customizing a spare field with a
formula
that calculates a date difference, or it could done a little
more
efficiently using VBA. For simplicity, let's use the custom
field
approach. You didn't mention if you want the difference in
working
days
or elapsed days - I'll assume working days.

1. Designate two spare fields - one spare date field (e.g.
Date1)
and
one spare duration field (e.g. Duration1)
2. Enter the date of "contact signing" in the Date1 field and do
a
fill
down so it is entered in all tasks. This is necessary since
formulas
only calculate on task by task basis
3. Go to Tools/Customize/Field
4. Select the field type as "Duration" and then select Duration1
5. Hit the Formula button
6. Insert the following
ProjDateDiff([Date1],[Start])
7. Hit "OK" and "OK"

Hope this helps.

John
Project MVP

:

On Nov 5, 7:02 am, Scrufnut
<[email protected]>
wrote:
I would like to add a column that shows the number of days
between a
specific
task (in this case -"contract signing") and the start of
every
other
task.
How do I do this? Is there an existing field type that
would
let
me
do
this?
Do I use a formula, and if so, where can I find basic
instructions on
how
to
create one?

Lemme see now, I think I did this once before. Oh yes, the
"Help"
function in Project will tell you! Glad I remembered that.