Workhours per month for a given period

[bstobart]

How can I determine the number of workhours in a given month, taking into
consideration whatever holidays have been entered into the Standard calendar?

I'm exporting data from MS-Project to MS-Excel where I'm interested in doing
some calculations. Something like the following should work, but the
Datediff function does not seem to have an interval of "workday". It just
has "weekday".

DateDiff("d", tsv.StartDate, tsv.EndDate)*8

where tsv is one of the timescale values in

Tsvs = oResource.TimeScaleData(first, _
last, Type:=pjResourceTimescaledWork, TimescaleUnit:=pjTimescaleMonths)

It would seem to be cleaner to start from the Calendar, rather than from a
Resource, since resources might have vacation days. I want the standard
hours, not an individual's hours. Any ideas?

 

[John]

How can I determine the number of workhours in a given month, taking into
consideration whatever holidays have been entered into the Standard calendar?

I'm exporting data from MS-Project to MS-Excel where I'm interested in doing
some calculations. Something like the following should work, but the
Datediff function does not seem to have an interval of "workday". It just
has "weekday".

DateDiff("d", tsv.StartDate, tsv.EndDate)*8

where tsv is one of the timescale values in

Tsvs = oResource.TimeScaleData(first, _
last, Type:=pjResourceTimescaledWork, TimescaleUnit:=pjTimescaleMonths)

It would seem to be cleaner to start from the Calendar, rather than from a
Resource, since resources might have vacation days. I want the standard
hours, not an individual's hours. Any ideas?

bstobart,
For working days in a month you can use the following:
DaysInMonth = Application.DateDifference([month start],[month end]) _
/ 60 + 8

This assumes a normal 8 hour work day.

John
Project MVP

 

[bstobart]

Thanks John, your solution worked for me perfectly.

I find it strange that the documentation for the DateDifference function did
not mention that it includes only work time. When I first read your
response, I assumed you had misunderstood me, but when I tried your solution,
it worked.

 

[John]

Thanks John, your solution worked for me perfectly.

I find it strange that the documentation for the DateDifference function did
not mention that it includes only work time. When I first read your
response, I assumed you had misunderstood me, but when I tried your solution,
it worked.

bstobart,
You're welcome and thanks for the feedback.

I agree, it would be nice if the object browser gave more definitive
information. By now you probably know there is a date difference method
for Project (it gives the difference in working time) and a separate
date difference method for VBA (it gives the difference in calendar
time).

John
Project MVP