How to get the holidays for a given month in VBA

[KarthikK]

Hello ,

I have a requirement where in I need to get the distrubution of horus for a
parent task from its start date to end date (like the Track usage view).

For this i am looping through each child tasks and applying the following
logic.
1. Find the number of working days between the start and end date
2. Divide Work/No of working days (overall). This will give me the hours per
day
3. Then find the number of working days for each month in the specified range
for example: start date: 01/01/2007 and end date 31/03/2007 then i get the
workingdays for each month and the do my calculation to get the effort
distribution.

But the problem there are some holidays set in the calendar for the month of
Jan and March and i need to consider them.
Can you one tell me how to find the list of holidays for a given date range
using the specified calendar in the project settings?

best regards,
Karthik

 

[John]

Hello ,

I have a requirement where in I need to get the distrubution of horus for a
parent task from its start date to end date (like the Track usage view).

For this i am looping through each child tasks and applying the following
logic.
1. Find the number of working days between the start and end date
2. Divide Work/No of working days (overall). This will give me the hours per
day
3. Then find the number of working days for each month in the specified range
for example: start date: 01/01/2007 and end date 31/03/2007 then i get the
workingdays for each month and the do my calculation to get the effort
distribution.

But the problem there are some holidays set in the calendar for the month of
Jan and March and i need to consider them.
Can you one tell me how to find the list of holidays for a given date range
using the specified calendar in the project settings?

best regards,
Karthik

Karthik,
If all you want is the distribution of working hours for summary lines,
I suggest you apply the "summary tasks" filter and then loop through
those lines using the TimeScaleData Method. It will automatically take
into account non-working time (weekends, holidays, etc.).

Nonetheless, if I have missed something in interpreting your end goal,
there are several ways to determine the the number of working days per
month (or whatever time period). One of those methods is using the
following method wherein the start and finish are the start and end of
each month.
Application.DateDifference(Start,Finish)/480+1

Another method would be to use something like the following:
Sub workingdays()
Dim m As Month
Dim d As Day
Dim DPM As Integer
For Each m In activeproject.Calendar.Years(2007).Months
DPM = 0
For Each d In m.Days
If d.Working = True Then DPM = DPM + 1
Next d
Debug.Print m.name & " - " & DPM & " working days"
Next m
End Sub

Hope this helps.
John
Project MVP

 

[Jan De Messemaeker]

Hi,

May I ask why you don't just use the Task Usage view? It tells you the
distribution of working hours (day by day or hour buy hour or week by
week..) also for the summary tasks.
HTH

 

[John]

Hi
Thanks for your reply.

I want this data to be imported from MS project to another database for some
metrics calculation so i need the data exactly as the "Track usage " view
shows. I came to know that the Track usage view does all these calculations
(taking holidays in to considerations) internally.
Any suggestions?

regards,
Karthik

Karthik,
I already gave some suggestions in my first reply. Did you try those?

John
Project MVP

 

[Jan De Messemaeker]

Hi,

It's always a bit disturbing when a personal vocabulary is used. I have
assumed that you mean the Task Usage view and not the Tracking Gantt.
A view doesn't do any calculation, it merely shows what the scheduling
engine has calculated and which is used for all views.

As to your question:

If you want to know the work distribution for any task (including summary
tasks) use the TimeScaledata method (it has pages of Help).
Timescaledata are what you can observe in a task usage view.
If you want to know whether a day is nonworking use
Resource.Calendar.period(theday).working (but I do not think you need that
for the result you want)

Your calculation logic seems not appropriate, sorry.

 

[KarthikK]

Hello Jan,
Yes i am aware that the "Task Usage view" doesn't do any special
calculations, I am only interested in how to get the data the same view the
view does.

But the TimeScaledata Method, the one you suggested was the one I was
looking for.

Thanks a lot. This saved lot of my time

best regards,
Karthik