Creating a calendar with varying weeks per month

[JMontgomery]

Is it possible to create a custom calendar with different numbers of weeks
per month?

Where I work, we are on a fiscal year calendar that goes from Oct. 1 to Sep.
30.

For Fiscal Year (FY) 08, there are, for example, 4 weeks in Oct, 4 weeks in
November and 5 weeks in December and our work weeks begin on Monday.

Oct goes from Monday Oct 1 to Sunday Oct 28 (4 weeks)
Nov goes from Monday Oct 29 to Sunday Nov 25 (4 weeks)
Dec goes from Monday Nov 26 to Sunday Dec 30 (5 weeks)

I can't figure out a way to set up a fiscal year calendar that has different
numbers of weeks per month. It seems like you have to always have a constant
number of days per month as settable in the Calendar tab of the Options pull
down menu item.

Is there a way in Project to define a fiscal year calendar that can have
varying weeks per month and start on days that may not be the 1st day of a
given month?

 

[John]

Is it possible to create a custom calendar with different numbers of weeks
per month?

Where I work, we are on a fiscal year calendar that goes from Oct. 1 to Sep.
30.

For Fiscal Year (FY) 08, there are, for example, 4 weeks in Oct, 4 weeks in
November and 5 weeks in December and our work weeks begin on Monday.

Oct goes from Monday Oct 1 to Sunday Oct 28 (4 weeks)
Nov goes from Monday Oct 29 to Sunday Nov 25 (4 weeks)
Dec goes from Monday Nov 26 to Sunday Dec 30 (5 weeks)

I can't figure out a way to set up a fiscal year calendar that has different
numbers of weeks per month. It seems like you have to always have a constant
number of days per month as settable in the Calendar tab of the Options pull
down menu item.

Is there a way in Project to define a fiscal year calendar that can have
varying weeks per month and start on days that may not be the 1st day of a
given month?

JMontgomery,
What you are looking for is an accounting month calendar and it looks
like you are using a 4-4-5. The only way to get that with Project is
through VBA and export to another application. I've written two such
macros - once at the company where I worked and later for a client.

John
Project MVP

 

[John]

I think the question should be not how, but why?
On my planet, October goes from 1/10 - 31/10, November 1/11 - 30/11,
December 1/12 - 31/12.
MSP agrees.

The constant number of days per month that you set in Tools, Options... is
just a conversion factor so that MSP knows what you mean if you type "3 mo"
into a Duration. MSP converts all Durations to minutes, performs
calculations in minutes and then re-converts back when it spits out the
answers.
eg, 1 month = 20 days = 20 x 8 x 60 minutes
Since it is a good idea to stick to just one consistent unit such as Days
for Duration, this conversion factor for months doesn't really matter
because it never comes up.

In that case, I don't see that you have any problem to solve.
There is no need to try to force MSP to understand your accounting system's
weird re-definition of October, November and December.

You could use Tools, Change Working time to set up a project, task or
resource calendar which cuts out extra non working days.

Trevor Rabey 0407213955 61 8 92729485 PERFECT PROJECT PLANNING
www.perfectproject.com.au

Trevor,
Why? Because on the same planet where you live there are companies that
use accounting month calendars. That is what J Montgomery is asking for.
And unfortunately the Project developers have never included that
flexibility in any version of Project.

John

 

[JMontgomery]

Trevor,
Why? Because on the same planet where you live there are companies that
use accounting month calendars. That is what J Montgomery is asking for.
And unfortunately the Project developers have never included that
flexibility in any version of Project.

John

Thanks for confirming what I thought was the case, John.

John has pointed out the exact reason I had hoped Project supported what I
ask for. I am responsible for planning out the cost and schedule for a team
of people and we track workforce by something called FTEs (Full Time
Equivalents). 1.0 FTE equals 1 person for 100% effort for a given duration
of time. In my example below, 1 FTE is worth more hours in December (5 weeks
of work) vs work in Oct or Nov (4 weeks of work). Specifically, in Oct we
have 143 hours of work, Nov has 127 hours of work and Dec has 161 hours of
work available.

I used Project to lay in my people, their tasks, durations and hours of
effort. Project spits outs hours per week over the task durations. I had
hoped Project would allow me to group these hours by fiscal month and I could
then easily convert from hours to FTEs to enter into our cost estimation
tools.

Unfortunately, due to the limitation in Project, I've had to write an Excel
spread sheet that defines my fiscal months, copy/paste the hours/week for
each person for each task for each time period into it and then compute hours
per fiscal month and then finally FTEs based upon the hours per month
available for each month.

I wished I lived on a planet that either didn't plan via fiscal month or on
a planet where Project support planning by fiscal month ;-)

Microsoft if you are listening, it would be great if you could modify
Project to support the definition of calendars that support varying days per
month and more specially, the ability to define fiscal months that may or may
not start on the first day of a given month.

jim

 

[JMontgomery]

JMontgomery,
What you are looking for is an accounting month calendar and it looks
like you are using a 4-4-5. The only way to get that with Project is
through VBA and export to another application. I've written two such
macros - once at the company where I worked and later for a client.

John
Project MVP

John,

I'm not familiar with VBA.

As my earlier post mentioned, I have written an excel spreadsheet to compute
my FTEs for me. Currently, I tediously copy/paste all the hours for a given
person for a given task for a given duration into my spreadsheet to group the
hours by fiscal month and then compute an FTE for that month.

I've tried to figure out ways to export the data in project into an excel
spreadsheet that would output the hours per week, but it only seems to allow
exporting total hours for a given task for a given person (resource). About
the best I have been able to do is to in project sort by person (resource)
and then manually copy/paste the hours for that resource for that task into
my excel spreadsheet from project. Don't know if this is clear or not.

Regardless, thanks for answering my original question. Maybe Microsoft will
take mercy on me (and the others that plan by fiscal month) and modify
project to support this type of planning. One can hope....

jim

 

[John]

John,

I'm not familiar with VBA.

As my earlier post mentioned, I have written an excel spreadsheet to compute
my FTEs for me. Currently, I tediously copy/paste all the hours for a given
person for a given task for a given duration into my spreadsheet to group the
hours by fiscal month and then compute an FTE for that month.

I've tried to figure out ways to export the data in project into an excel
spreadsheet that would output the hours per week, but it only seems to allow
exporting total hours for a given task for a given person (resource). About
the best I have been able to do is to in project sort by person (resource)
and then manually copy/paste the hours for that resource for that task into
my excel spreadsheet from project. Don't know if this is clear or not.

Regardless, thanks for answering my original question. Maybe Microsoft will
take mercy on me (and the others that plan by fiscal month) and modify
project to support this type of planning. One can hope....

jim

Jim,
VBA is Visual Basic for Applications. It is included with every Office
application and opens up a whole new world of functionality.

The macros I wrote automate everything you are doing manually and more.
They take the Project data for each task or resource, parse it into
accounting months, export it to Excel, and then produce and format
various reports, one of which is FTEs.

As an alternate to a full VBA approach, you might want to look at the
"analyze timescale data in Excel" utility found on the "Analysis"
toolbar. If you run it from the Resource Usage view, it will export
timescaled data, (you can set it for weeks), for each resource. You can
then take that data in Excel and parse it into accounting months and
calculate the FTE value. It is not as convenient as a custom macro but
it is a whole lot better than copy and paste.

A few years ago I asked some powers that be at Microsoft if there were
any plans for adding the accounting month flexibility to Project. The
response I got back was that there were no plans. It doesn't mean they
won't do it but my guess is that such a small percentage of users work
with accounting months that adding the flexibility is not worth the
development effort.

John
Project MVP

 

[JMontgomery]

Jim,
VBA is Visual Basic for Applications. It is included with every Office
application and opens up a whole new world of functionality.

The macros I wrote automate everything you are doing manually and more.
They take the Project data for each task or resource, parse it into
accounting months, export it to Excel, and then produce and format
various reports, one of which is FTEs.

As an alternate to a full VBA approach, you might want to look at the
"analyze timescale data in Excel" utility found on the "Analysis"
toolbar. If you run it from the Resource Usage view, it will export
timescaled data, (you can set it for weeks), for each resource. You can
then take that data in Excel and parse it into accounting months and
calculate the FTE value. It is not as convenient as a custom macro but
it is a whole lot better than copy and paste.

A few years ago I asked some powers that be at Microsoft if there were
any plans for adding the accounting month flexibility to Project. The
response I got back was that there were no plans. It doesn't mean they
won't do it but my guess is that such a small percentage of users work
with accounting months that adding the flexibility is not worth the
development effort.

John
Project MVP

John,

Thanks very much for this latest info! It is extremely helpful and is going
to save me alot of time!

Is there any way possible to get copies of the VBA macros you mention? They
sound extremely similar to the very problem I am trying to solve and would
save me even more time.

I'll make sure to ask Microsoft the same question you did It may not make a
difference to hear just one more voice asking for the functionality but you
never know.

tnx,
jim

 

[John]

John,

Thanks very much for this latest info! It is extremely helpful and is going
to save me alot of time!

Is there any way possible to get copies of the VBA macros you mention? They
sound extremely similar to the very problem I am trying to solve and would
save me even more time.

I'll make sure to ask Microsoft the same question you did It may not make a
difference to hear just one more voice asking for the functionality but you
never know.

tnx,
jim

Jim,
You're welcome. We are here to help.

The macros I mentioned are NOT freeware. If you are still interested you
may contact me direct at the address below.

John
Project MVP
jensenj6atatcomcastdotdotnet
(remove obvious redundancies)