custom columns for WKS

[Penny]

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

 

[Andrew Lavinsky]

I don't have a copy of MS Project available at the moment, but in theory, you
could create a custom text field using a formula like the following:

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

....or something like that. I'd have to test it in Project to make sure the
syntax is correct. I'd also have to assume the project started on a Monday -
otherwise, some of the results may be a bit skewed.

 

[Jim Aksel]

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

 

[Sai]

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

Penny - Correct version of Jim Aksel works. If you would like to
change your timescale (by default, the top tier displays the week
start date -- Feb 7 10, and bottom tier displays weekdays -- S, M,
T, ...), to displays the week numbers instead, then follow the below
steps

1. SelectFormat, Timescale
2. Select Middle Tier, and change the Label: to W1, W2, ...

Please let us know if this satisfies your requirement

- Sai, PMP, PMI-SP, MCT, MCTS
http://saipower.wordpress.com

 

[Penny]

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

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

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

 

[Andrew Lavinsky]

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

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

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

 

[Sai]

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

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
:

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

 

[Penny]

The week is starting on Monday and the start date is Feb 1.

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

:

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
:

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
.

 

[Jim Aksel]

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

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

:

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
:

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
.

 

[Penny]

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.

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

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

:

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
:

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
.

 

[Andrew Lavinsky]

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

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

:

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
:
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

 

[Andrew Lavinsky]

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

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

:

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
:
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
.

 

[Penny]

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.

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
:
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

:

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
:
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
.

.

 

[JulieS]

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

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.

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

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

:

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
:

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
.

 

[Andrew Lavinsky]

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.

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
:
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

:

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
:
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
.

.

 

[Andrew Lavinsky]

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.

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
:
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

:

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
:
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
.
.