Problematic formula !

[Ian Wolstenholme]

Hello

I have followed the instructions for creating a new "Text" column and
entering in the Day(DateDiff("d",[Start],[Finish])+1) & " days" formula and
the formula was accepted - but the answers do not make sense - for
instance....

a.. Milestone activities "0 days" duration come out as "31 days" elapsed
duration..
b.. A Start (03 Jun05) and Finish(27 May09) comes out as "6 days" ?

Ideally, I would like the new field(column) to provide elapsed time in weeks
between Start and Finish , I would like the new field(column) to provide
elapsed time in weeks between Start and Finish.

I'd appreciate any advice/help.

Thanks

Ian

 

[JackD]

It does not make sense because your formula does not make sense.
Why are you using Day(...)?
Day returns the day of the month. So for example if I have a formula that is
Day("5/31/2005")
it returns 31

If you get rid of the day then it should be fine.

 

[JackD]

Oh, for weeks I think you can use DateDiff("w",,,) or simply divide by 7.

 

[Ian Wolstenholme]

Hi Jack,

Thanks, I dropped the Day, but the formula still returns a value of 1d for
all milestone activities...

Can you shed any light on why this might be ?

Thanks

Ian

It does not make sense because your formula does not make sense.
Why are you using Day(...)?
Day returns the day of the month. So for example if I have a formula that
is
Day("5/31/2005")
it returns 31

If you get rid of the day then it should be fine.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
.

Hello

I have followed the instructions for creating a new "Text" column and
entering in the Day(DateDiff("d",[Start],[Finish])+1) & " days" formula and
the formula was accepted - but the answers do not make sense - for
instance....

a.. Milestone activities "0 days" duration come out as "31 days"
elapsed
duration..
b.. A Start (03 Jun05) and Finish(27 May09) comes out as "6 days" ?

Ideally, I would like the new field(column) to provide elapsed time in weeks
between Start and Finish , I would like the new field(column) to provide
elapsed time in weeks between Start and Finish.

I'd appreciate any advice/help.

Thanks

Ian

 

[JackD]

Because you are returning the difference between [Finish]+1 and [Start].
You can certainly understand when the finish and start are the same that
your formula evaluates to 1.
--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Hi Jack,

Thanks, I dropped the Day, but the formula still returns a value of 1d for
all milestone activities...

Can you shed any light on why this might be ?

Thanks

Ian

It does not make sense because your formula does not make sense.
Why are you using Day(...)?
Day returns the day of the month. So for example if I have a formula that
is
Day("5/31/2005")
it returns 31

If you get rid of the day then it should be fine.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
.

Hello

I have followed the instructions for creating a new "Text" column and
entering in the Day(DateDiff("d",[Start],[Finish])+1) & " days" formula and
the formula was accepted - but the answers do not make sense - for
instance....

a.. Milestone activities "0 days" duration come out as "31 days"
elapsed
duration..
b.. A Start (03 Jun05) and Finish(27 May09) comes out as "6 days" ?

Ideally, I would like the new field(column) to provide elapsed time in weeks
between Start and Finish , I would like the new field(column) to provide
elapsed time in weeks between Start and Finish.

I'd appreciate any advice/help.

Thanks

Ian

 

[Brian K - Project MVP]

Hi Jack,

Thanks, I dropped the Day, but the formula still returns a value of
1d for all milestone activities...

Can you shed any light on why this might be ?

You were adding 1. Try this

IIf([Milestone]=True,"0 days",DateDiff("d",[Start],[Finish]+1) & "
days")