Calculate work hours in a month 1

[darall]

I have a database where resources enter forecasted project work time for each month. I need a way to automatically calculate the available work hours for a given month.

I will then subtract the available work hours for the month from the forecast hours entered to come up with the non-forecasted hours.

Available work hours = number of weekdays in the month times 8.
forecast hours = hours entered by resources each month.
non-forecasted hours = Available work hours - Forecasted Hours

For example, if a resource forecasts 100 hours in February, there would be a total of 60 hours of non-forecasted time.

Here are the tables/fields in my database:

tblForecastHours
intResourceID = links to a table of resources
dtmForecastMonth = month being forecasted against
intMonth01 = hours forecasted to the current month

tblResources
intResourceID
strResourceFN
strResourceLN

I am not sure how to come up with the available work hours, any help would be appreciated,
Denae

 

[Zion7]

from the top of my head, here's a small function.


Function AvWorkDays(dteDate As Date) As Integer

Dim dteLastDay As Date
Dim intDays As Integer

dteLastDay = DateSErial(Year(dteDate),Month(dteDate) + 1,0)

Do Until dteDate = dteLastDay
Select Case Weekday(dteDate)
Case 1 To 5: intDays = intDays + 1
End SElect
dteDate = dteDate + 1
Loop

AvWorkDays = intDays

End Function


Typed not tested.
Check my case statement, maybe it's "Case 3 To 7"?

 

[Zion7]

Ooops!, forgot to add, firstday.

Function AvWorkDays(dteDate As Date) As Integer

Dim dteLastDay As Date, dteFirstDay As Date
Dim intDays As Integer


dteFirstDay = DateSErial(Year(dteDate),Month(dteDate) ,1)
dteLastDay = DateSErial(Year(dteDate),Month(dteDate) + 1,0)

Do Until dteFirstDay = dteLastDay
Select Case Weekday(dteFirstDay )
Case 1 To 5: intDays = intDays + 1
End SElect
dteFirstDay = dteFirstDay + 1
Loop

AvWorkDays = intDays

End Function

 

[Zion7]

Okay, finally got it..

Function AvWorkDays(dteDate As Date) As Integer

Dim dteLastDay As Date, dteFirstDay As Date
Dim intDays As Integer


dteFirstDay = DateSerial(Year(dteDate), Month(dteDate), 1)
dteLastDay = DateSerial(Year(dteDate), Month(dteDate) + 1, 1)

Do Until dteFirstDay = dteLastDay
Select Case Weekday(dteFirstDay)
Case 2 To 6: intDays = intDays + 1
End Select
dteFirstDay = dteFirstDay + 1
Loop

AvWorkDays = intDays

End Function

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmm ... but ... but ... but

Not all non-"weekdays" in all months are work days, so yoiu need something kike DerltaDays (see faq181-261) to also exclude any "holiday" days in the interval. Then, there is the simple (but beguiling issue) of hours in a work-day, not to mention the number of employees.

Beyond these are hte perhaps more esoteric (or subtle?) of mandantory overtime, shift work, what I refer to a SLL (more properly called PTO in these days and times) and wheather some of this is done by forcast or only actual (and if the latter, who will update this and how often?)

But still a good "College Try".



MichaelRed

 

[darall]

This is exactly what I needed, thank you Zion7!