Prev: how do i reset the Unique ID field in Project 2003?
Next: Macro for filter
From: Andrew Lavinsky on 28 Jan 2010 13:43
As always, the devil is in the details. The round function will round up
numbers like 1.6, and round down numbers like 1.4 - which was causing the
issue. I am not sure what the roundup formula is in MPP (as it's not the
same as in Excel). So I did a bit of an inelegant solution here:

IIf([Project Start]=[Start],1,IIf(Round(ProjDateDiff([Project Start],[Start],"5
Day Calendar")/[Minutes Per Week])<ProjDateDiff([Project Start],[Start],"5
Day Calendar")/[Minutes Per Week],Round(ProjDateDiff([Project Start],[Start],"5
Day Calendar")/[Minutes Per Week])+1,Round(ProjDateDiff([Project Start],[Start],"5
Day Calendar")/[Minutes Per Week])))

This works fine, provided that you have a calendar set up as "5 Day Calendar"

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

> 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  | 
Pages: 1 2 3 4
Prev: how do i reset the Unique ID field in Project 2003?
Next: Macro for filter