Prev: how do i reset the Unique ID field in Project 2003?
Next: Macro for filter
From: Andrew Lavinsky on 28 Jan 2010 08:56
I didn't realize that you could use [Minutes Per Week] although I guess that
makes sense. Sai, you should note that the ProjDateDiff will count the business
days between two dates based on a calendar you specify. If you don't specify
a calendar, it will default to the Project Calendar, which is usually a 5
day calendar.

That being said, some of the issues that Penny may have had might have been
caused by any holidays inserted into the Project Calendar. So Penny, if
you plan to use nonworking time and calendars, I would suggest one more minor
change to the formula - and then it seems to work in my environment.

"WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day Calendar")/(5*[Minutes
Per Day]),0)+1

Where "5 Day Calendar" is a new calendar you must create in Tools > Change
Working Time that is a copy of the Standard, and which has no holidays inserted.
As long as you don't change the calendar, that calculation seems to work.

If you end up moving the Project Start Date from a Monday, you may also need
to remove the offset, but that shouldn't be an issue in this case.


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

> Oops, that "7" needs to be a "5" as ProjDateDiff calculates business
> days. More technically, it should not be 5 either:
>
> 5*[Minutes Per Day] should read [Minutes Per Week] with no integer at
> all.
>
> Thanks for keeping me on my toes.
>
> Jim Aksel, MVP
>
> Check out my blog for more information:
> http://www.msprojectblog.com
> "Sai" wrote:
>
>> Andrew Lavinsky Catapult Systems wrote:
>>
>>> What's the first day of the first week in the Gantt, and what is
>>> your Project Start Date? We probably need to do a bit more "base
>>> offsetting" in the formula to match the two up.
>>>
>>> - Andrew Lavinsky
>>> Blog: http://blogs.catapultsystems.com/epm
>>>> Thank you very much for the feedback. I used this exact formula
>>>> and was very pleased that I got mostly the results I was looking
>>>> for. I was hoping to post a picture of what I got but can't. I
>>>> did get the wks in the columns. But they are not exactly lining up
>>>> with the wks in the gantt. What kind of adjustments do I need to
>>>> make?
>>>>
>>>> Thanks again.
>>>>
>>>> Penny
>>>>
>>>> "Jim Aksel" wrote:
>>>>
>>>>> Andrew has it with one additional thing...
>>>>> "WK" & Round(ProjDateDiff([Project Start],[Start])/(5*[Minutes Per
>>>>> Day]),0) +1
>>>>> You need to add the 1 at the end becuase the ProjDateDiff function
>>>>> returns 0 for the week containing the project start date. So it
>>>>> is a
>>>>> "0 base offset" in geek terms.
>>>>> The formula works fine regardless of start date day of week.
>>>>>
>>>>> Thanks Andrew!
>>>>> --
>>>>> If this post was helpful, please consider rating it.
>>>>> Jim Aksel, MVP
>>>>> Check out my blog for more information:
>>>>> http://www.msprojectblog.com
>>>>> "Penny" wrote:
>>>>>> I would like to create a formula in a custom field that tells me
>>>>>> what week my start date is in. I have my gantt set up to show
>>>>>> WK1, WK2, etc. I would like to have that same week designation
>>>>>> in a custom column. Can I do that?
>>>>>>
>>>>>> thanks.
>>>>>>
>>>>>> Penny
>>>>>>
>> Can you change the formula to "WK" & Round(ProjDateDiff([Project
>> Start],[Start])/(7*[Minutes Per Day]),0) +1
>>
>> Sai, PMP PMI-SP MCT MCTS
>> .


From: Andrew Lavinsky on 28 Jan 2010 09:02
Forgot to change it to minutes per week (but actually I don't think it matters).
I guess it's a bit more elegant that way:

"WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day Calendar")/([Minutes
Per Week]),0)+1


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

> I didn't realize that you could use [Minutes Per Week] although I
> guess that makes sense. Sai, you should note that the ProjDateDiff
> will count the business days between two dates based on a calendar you
> specify. If you don't specify a calendar, it will default to the
> Project Calendar, which is usually a 5 day calendar.
>
> That being said, some of the issues that Penny may have had might have
> been caused by any holidays inserted into the Project Calendar. So
> Penny, if you plan to use nonworking time and calendars, I would
> suggest one more minor change to the formula - and then it seems to
> work in my environment.
>
> "WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day
> Calendar")/(5*[Minutes Per Day]),0)+1
>
> Where "5 Day Calendar" is a new calendar you must create in Tools >
> Change
> Working Time that is a copy of the Standard, and which has no holidays
> inserted.
> As long as you don't change the calendar, that calculation seems to
> work.
> If you end up moving the Project Start Date from a Monday, you may
> also need to remove the offset, but that shouldn't be an issue in this
> case.
>
> - Andrew Lavinsky
> Blog: http://blogs.catapultsystems.com/epm
>> Oops, that "7" needs to be a "5" as ProjDateDiff calculates business
>> days. More technically, it should not be 5 either:
>>
>> 5*[Minutes Per Day] should read [Minutes Per Week] with no integer at
>> all.
>>
>> Thanks for keeping me on my toes.
>>
>> Jim Aksel, MVP
>>
>> Check out my blog for more information:
>> http://www.msprojectblog.com
>> "Sai" wrote:
>>> Andrew Lavinsky Catapult Systems wrote:
>>>
>>>> What's the first day of the first week in the Gantt, and what is
>>>> your Project Start Date? We probably need to do a bit more "base
>>>> offsetting" in the formula to match the two up.
>>>>
>>>> - Andrew Lavinsky
>>>> Blog: http://blogs.catapultsystems.com/epm
>>>>> Thank you very much for the feedback. I used this exact formula
>>>>> and was very pleased that I got mostly the results I was looking
>>>>> for. I was hoping to post a picture of what I got but can't. I
>>>>> did get the wks in the columns. But they are not exactly lining
>>>>> up with the wks in the gantt. What kind of adjustments do I need
>>>>> to make?
>>>>>
>>>>> Thanks again.
>>>>>
>>>>> Penny
>>>>>
>>>>> "Jim Aksel" wrote:
>>>>>
>>>>>> Andrew has it with one additional thing...
>>>>>> "WK" & Round(ProjDateDiff([Project Start],[Start])/(5*[Minutes
>>>>>> Per
>>>>>> Day]),0) +1
>>>>>> You need to add the 1 at the end becuase the ProjDateDiff
>>>>>> function
>>>>>> returns 0 for the week containing the project start date. So it
>>>>>> is a
>>>>>> "0 base offset" in geek terms.
>>>>>> The formula works fine regardless of start date day of week.
>>>>>> Thanks Andrew!
>>>>>> --
>>>>>> If this post was helpful, please consider rating it.
>>>>>> Jim Aksel, MVP
>>>>>> Check out my blog for more information:
>>>>>> http://www.msprojectblog.com
>>>>>> "Penny" wrote:
>>>>>>> I would like to create a formula in a custom field that tells me
>>>>>>> what week my start date is in. I have my gantt set up to show
>>>>>>> WK1, WK2, etc. I would like to have that same week designation
>>>>>>> in a custom column. Can I do that?
>>>>>>>
>>>>>>> thanks.
>>>>>>>
>>>>>>> Penny
>>>>>>>
>>> Can you change the formula to "WK" & Round(ProjDateDiff([Project
>>> Start],[Start])/(7*[Minutes Per Day]),0) +1
>>>
>>> Sai, PMP PMI-SP MCT MCTS
>>> .


From: Penny on 28 Jan 2010 09:55
Andrew can I communicate with you through email. I get an error message with
this formula if I try and type it in exactly like you are indicating. If I
copy and paste your formula the "Minutes Per Week" is giving me an error.

"Andrew Lavinsky" wrote:

> Forgot to change it to minutes per week (but actually I don't think it matters).
> I guess it's a bit more elegant that way:
>
> "WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day Calendar")/([Minutes
> Per Week]),0)+1
>
>
> - Andrew Lavinsky
> Blog: http://blogs.catapultsystems.com/epm
>
> > I didn't realize that you could use [Minutes Per Week] although I
> > guess that makes sense. Sai, you should note that the ProjDateDiff
> > will count the business days between two dates based on a calendar you
> > specify. If you don't specify a calendar, it will default to the
> > Project Calendar, which is usually a 5 day calendar.
> >
> > That being said, some of the issues that Penny may have had might have
> > been caused by any holidays inserted into the Project Calendar. So
> > Penny, if you plan to use nonworking time and calendars, I would
> > suggest one more minor change to the formula - and then it seems to
> > work in my environment.
> >
> > "WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day
> > Calendar")/(5*[Minutes Per Day]),0)+1
> >
> > Where "5 Day Calendar" is a new calendar you must create in Tools >
> > Change
> > Working Time that is a copy of the Standard, and which has no holidays
> > inserted.
> > As long as you don't change the calendar, that calculation seems to
> > work.
> > If you end up moving the Project Start Date from a Monday, you may
> > also need to remove the offset, but that shouldn't be an issue in this
> > case.
> >
> > - Andrew Lavinsky
> > Blog: http://blogs.catapultsystems.com/epm
> >> Oops, that "7" needs to be a "5" as ProjDateDiff calculates business
> >> days. More technically, it should not be 5 either:
> >>
> >> 5*[Minutes Per Day] should read [Minutes Per Week] with no integer at
> >> all.
> >>
> >> Thanks for keeping me on my toes.
> >>
> >> Jim Aksel, MVP
> >>
> >> Check out my blog for more information:
> >> http://www.msprojectblog.com
> >> "Sai" wrote:
> >>> Andrew Lavinsky Catapult Systems wrote:
> >>>
> >>>> What's the first day of the first week in the Gantt, and what is
> >>>> your Project Start Date? We probably need to do a bit more "base
> >>>> offsetting" in the formula to match the two up.
> >>>>
> >>>> - Andrew Lavinsky
> >>>> Blog: http://blogs.catapultsystems.com/epm
> >>>>> Thank you very much for the feedback. I used this exact formula
> >>>>> and was very pleased that I got mostly the results I was looking
> >>>>> for. I was hoping to post a picture of what I got but can't. I
> >>>>> did get the wks in the columns. But they are not exactly lining
> >>>>> up with the wks in the gantt. What kind of adjustments do I need
> >>>>> to make?
> >>>>>
> >>>>> Thanks again.
> >>>>>
> >>>>> Penny
> >>>>>
> >>>>> "Jim Aksel" wrote:
> >>>>>
> >>>>>> Andrew has it with one additional thing...
> >>>>>> "WK" & Round(ProjDateDiff([Project Start],[Start])/(5*[Minutes
> >>>>>> Per
> >>>>>> Day]),0) +1
> >>>>>> You need to add the 1 at the end becuase the ProjDateDiff
> >>>>>> function
> >>>>>> returns 0 for the week containing the project start date. So it
> >>>>>> is a
> >>>>>> "0 base offset" in geek terms.
> >>>>>> The formula works fine regardless of start date day of week.
> >>>>>> Thanks Andrew!
> >>>>>> --
> >>>>>> If this post was helpful, please consider rating it.
> >>>>>> Jim Aksel, MVP
> >>>>>> Check out my blog for more information:
> >>>>>> http://www.msprojectblog.com
> >>>>>> "Penny" wrote:
> >>>>>>> I would like to create a formula in a custom field that tells me
> >>>>>>> what week my start date is in. I have my gantt set up to show
> >>>>>>> WK1, WK2, etc. I would like to have that same week designation
> >>>>>>> in a custom column. Can I do that?
> >>>>>>>
> >>>>>>> thanks.
> >>>>>>>
> >>>>>>> Penny
> >>>>>>>
> >>> Can you change the formula to "WK" & Round(ProjDateDiff([Project
> >>> Start],[Start])/(7*[Minutes Per Day]),0) +1
> >>>
> >>> Sai, PMP PMI-SP MCT MCTS
> >>> .
>
>
> .
>
From: JulieS on 28 Jan 2010 09:55
Pardon me for bumping in. Penny, are you showing one more than the
weeks sometimes and sometimes the correct number? I believe the problem
is coming from the Round function -- it rounds up if the task begins
after mid week. I believe I've come up with an answer that will work.
Please note, I've spread the calculations over three fields to make the
error trapping a bit easier.

In Text1: ProjDateDiff([Project Start],[Start])/[Minutes Per Week]+1

This calculates the week the task begins in, however it shows decimals.
So if the Project begins on 2/1 and task 2 starts on 2/2, the value
shows 1.2. However, we only want to show the value to the left of the
decimal place, so

In Text2: IIf(Instr([Text1],".")=0,[Text1],Instr([Text1],".")-1)

This formula calculates the position of the decimal place in the Text1
field. In the case of any task beginning on the first day of a week,
the decimal is not there, so we just return the value in the text1
field. If the task begins on some day other than the first day of the
week, the formula calculates the number of characters to the left of the
decimal point.

In Text3: "WK " & Left([Text1],[Text2])
This adds the text "WK and a space to characters to the left of the
decimal place of the value in Text1.

I imagine you can combine them all into one field with a very long
formula, but as I noted, spreading the calculations over 3 fields helps
in troubleshooting.

Of course, I could be missing something very obvious, but in testing
with tasks of varying durations, this process seemed to consistently
return the value which matched the week beginning shown in the Gantt chart.

Julie
Project MVP

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

On 1/28/2010 8:38 AM, Penny wrote:
> Okay so I have changed to the Minutes Per Week. I have done the 7, then the
> 5, then none at all. I am still not matching with the wks shown in the gantt
> with any of these different combinations.
>
> "Jim Aksel" wrote:
>
>> Oops, that "7" needs to be a "5" as ProjDateDiff calculates business days.
>> More technically, it should not be 5 either:
>>
>> 5*[Minutes Per Day] should read [Minutes Per Week] with no integer at all.
>>
>> Thanks for keeping me on my toes.
>> --
>> If this post was helpful, please consider rating it.
>>
>> Jim Aksel, MVP
>>
>> Check out my blog for more information:
>> http://www.msprojectblog.com
>>
>>
>>
>> "Sai" wrote:
>>
>>> Andrew Lavinsky Catapult Systems wrote:
>>>> What's the first day of the first week in the Gantt, and what is your Project
>>>> Start Date? We probably need to do a bit more "base offsetting" in the formula
>>>> to match the two up.
>>>>
>>>>
>>>> - Andrew Lavinsky
>>>> Blog: http://blogs.catapultsystems.com/epm
>>>>
>>>>> Thank you very much for the feedback. I used this exact formula and
>>>>> was very pleased that I got mostly the results I was looking for. I
>>>>> was hoping to post a picture of what I got but can't. I did get the
>>>>> wks in the columns. But they are not exactly lining up with the wks
>>>>> in the gantt. What kind of adjustments do I need to make?
>>>>>
>>>>> Thanks again.
>>>>>
>>>>> Penny
>>>>>
>>>>> "Jim Aksel" wrote:
>>>>>
>>>>>> Andrew has it with one additional thing...
>>>>>> "WK"& Round(ProjDateDiff([Project Start],[Start])/(5*[Minutes Per
>>>>>> Day]),0) +1
>>>>>> You need to add the 1 at the end becuase the ProjDateDiff function
>>>>>> returns 0 for the week containing the project start date. So it is a
>>>>>> "0 base offset" in geek terms.
>>>>>>
>>>>>> The formula works fine regardless of start date day of week.
>>>>>>
>>>>>> Thanks Andrew!
>>>>>> --
>>>>>> If this post was helpful, please consider rating it.
>>>>>> Jim Aksel, MVP
>>>>>>
>>>>>> Check out my blog for more information:
>>>>>> http://www.msprojectblog.com
>>>>>> "Penny" wrote:
>>>>>>
>>>>>>> I would like to create a formula in a custom field that tells me
>>>>>>> what week my start date is in. I have my gantt set up to show WK1,
>>>>>>> WK2, etc. I would like to have that same week designation in a
>>>>>>> custom column. Can I do that?
>>>>>>>
>>>>>>> thanks.
>>>>>>>
>>>>>>> Penny
>>>>>>>
>>>
>>> Can you change the formula to "WK"& Round(ProjDateDiff([Project
>>> Start],[Start])/(7*[Minutes Per Day]),0) +1
>>>
>>>
>>> Sai, PMP PMI-SP MCT MCTS
>>> .
>>>
From: Andrew Lavinsky on 28 Jan 2010 10:09
Sure, it's andrew.lavinsky at the corporate URL at the bottom of this message.


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

> Andrew can I communicate with you through email. I get an error
> message with this formula if I try and type it in exactly like you are
> indicating. If I copy and paste your formula the "Minutes Per Week"
> is giving me an error.
>
> "Andrew Lavinsky" wrote:
>
>> Forgot to change it to minutes per week (but actually I don't think
>> it matters). I guess it's a bit more elegant that way:
>>
>> "WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day
>> Calendar")/([Minutes Per Week]),0)+1
>>
>> - Andrew Lavinsky
>> Blog: http://blogs.catapultsystems.com/epm
>>> I didn't realize that you could use [Minutes Per Week] although I
>>> guess that makes sense. Sai, you should note that the ProjDateDiff
>>> will count the business days between two dates based on a calendar
>>> you specify. If you don't specify a calendar, it will default to
>>> the Project Calendar, which is usually a 5 day calendar.
>>>
>>> That being said, some of the issues that Penny may have had might
>>> have been caused by any holidays inserted into the Project Calendar.
>>> So Penny, if you plan to use nonworking time and calendars, I would
>>> suggest one more minor change to the formula - and then it seems to
>>> work in my environment.
>>>
>>> "WK" & Round(ProjDateDiff([Project Start],[Start],"5 Day
>>> Calendar")/(5*[Minutes Per Day]),0)+1
>>>
>>> Where "5 Day Calendar" is a new calendar you must create in Tools >
>>> Change
>>> Working Time that is a copy of the Standard, and which has no
>>> holidays
>>> inserted.
>>> As long as you don't change the calendar, that calculation seems to
>>> work.
>>> If you end up moving the Project Start Date from a Monday, you may
>>> also need to remove the offset, but that shouldn't be an issue in
>>> this
>>> case.
>>> - Andrew Lavinsky
>>> Blog: http://blogs.catapultsystems.com/epm
>>>> Oops, that "7" needs to be a "5" as ProjDateDiff calculates
>>>> business days. More technically, it should not be 5 either:
>>>>
>>>> 5*[Minutes Per Day] should read [Minutes Per Week] with no integer
>>>> at all.
>>>>
>>>> Thanks for keeping me on my toes.
>>>>
>>>> Jim Aksel, MVP
>>>>
>>>> Check out my blog for more information:
>>>> http://www.msprojectblog.com
>>>> "Sai" wrote:
>>>>> Andrew Lavinsky Catapult Systems wrote:
>>>>>
>>>>>> What's the first day of the first week in the Gantt, and what is
>>>>>> your Project Start Date? We probably need to do a bit more "base
>>>>>> offsetting" in the formula to match the two up.
>>>>>>
>>>>>> - Andrew Lavinsky
>>>>>> Blog: http://blogs.catapultsystems.com/epm
>>>>>>> Thank you very much for the feedback. I used this exact formula
>>>>>>> and was very pleased that I got mostly the results I was looking
>>>>>>> for. I was hoping to post a picture of what I got but can't. I
>>>>>>> did get the wks in the columns. But they are not exactly lining
>>>>>>> up with the wks in the gantt. What kind of adjustments do I
>>>>>>> need to make?
>>>>>>>
>>>>>>> Thanks again.
>>>>>>>
>>>>>>> Penny
>>>>>>>
>>>>>>> "Jim Aksel" wrote:
>>>>>>>
>>>>>>>> Andrew has it with one additional thing...
>>>>>>>> "WK" & Round(ProjDateDiff([Project Start],[Start])/(5*[Minutes
>>>>>>>> Per
>>>>>>>> Day]),0) +1
>>>>>>>> You need to add the 1 at the end becuase the ProjDateDiff
>>>>>>>> function
>>>>>>>> returns 0 for the week containing the project start date. So
>>>>>>>> it
>>>>>>>> is a
>>>>>>>> "0 base offset" in geek terms.
>>>>>>>> The formula works fine regardless of start date day of week.
>>>>>>>> Thanks Andrew!
>>>>>>>> --
>>>>>>>> If this post was helpful, please consider rating it.
>>>>>>>> Jim Aksel, MVP
>>>>>>>> Check out my blog for more information:
>>>>>>>> http://www.msprojectblog.com
>>>>>>>> "Penny" wrote:
>>>>>>>>> I would like to create a formula in a custom field that tells
>>>>>>>>> me what week my start date is in. I have my gantt set up to
>>>>>>>>> show WK1, WK2, etc. I would like to have that same week
>>>>>>>>> designation in a custom column. Can I do that?
>>>>>>>>>
>>>>>>>>> thanks.
>>>>>>>>>
>>>>>>>>> Penny
>>>>>>>>>
>>>>> Can you change the formula to "WK" & Round(ProjDateDiff([Project
>>>>> Start],[Start])/(7*[Minutes Per Day]),0) +1
>>>>>
>>>>> Sai, PMP PMI-SP MCT MCTS
>>>>> .
>> .
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: how do i reset the Unique ID field in Project 2003?
Next: Macro for filter