Need help to calculat date between discarding weekends

[BaconMakinWabbit]

Hi all. I am a bit of a programmer but this is the first function in Access VBA. I need som help desperately.
I need to calculate hours between an open and a close time for a trouble ticket. The problem is I need to discard times after 4:30 PM and before 6:00 AM and I also have to discard weekends.
If the dates are the same I just need to calculate the datediff between to get minutes.
What I have attempted to do here (I think) is, if the days are the same then I will calculate the difference, which is fine. If there are days between I need to:
A) Calculate the hours in the opening day between opentime and 4:30PM (our closing time).
B) Add 10 hours (business hours for each day it is open, discarding weekends)
C) Calculate and add the closing day hours between 6:00 AM and the actual closing time.


Public Function TotalTime(pOpenDate As Date, pCloseDate As Date) As Integer


' Declare open date variables

Dim pOday As Integer
Dim pOhour As Integer
Dim pOminute As Integer
Dim pOyear As Integer
Dim pOmonth As Integer

' Declare close date variables

Dim pCday As Integer
Dim pChour As Integer
Dim pCminute As Integer
Dim pCyear As Integer
Dim pCmonth As Integer

' Declare calculation variables

Dim pDaysBetween As Integer
Dim pCounter As Integer
Dim pStartDay As Integer
Dim pTime As Integer
Dim pOpendayhours As Integer
Dim pClosedayhours As Integer

' Give variables a value based on parts of the date

pOday = DatePart("d", pOpenDate)
pOhour = DatePart("h", pOpenDate)
pOminute = DatePart("n", pOpenDate)
pOyear = DatePart("yyyy", pOpenDate)
pOmonth = DatePart("m", pOpenDate)


pCday = DatePart("d", pCloseDate)
pChour = DatePart("h", pCloseDate)
pCminute = DatePart("n", pCloseDate)
pCyear = DatePart("yyyy", pCloseDate)
pCmonth = DatePart("m", pCloseDate)


If (pOyear = pCyear) And (pOmonth = pCmonth) And (pOday = pCday) Then
TotalTime = DateDiff("n", pOpenDate, pCloseDate)
Else
pOpendayhours = Int(1630 - (pOhour) & (pOminute)) * 60
pClosedayhours = Int((pChour) & (pCminute) - 630) * 60
pStartDay = DatePart("w", pOpenDate, vbMonday) + 1
pCounter = DateDiff("d", pOpenDate, pCloseDate) - 1


' Declare counter
Dim i As Integer


' Begin loop to determine 10 hour per day per work day except
' the start and end day which we calculate actual hours
' We then add them all to get total hours between
For i = 1 To pCounter

pTime = pTime + 600

'If the day of the week is Sunday, make the day Monday
If pStartDay = 7 Then
pStartDay = 1
Else: pStartDay = pStartDay + 1
End If

Next i

End If

' Add Calculated time to the open and close times
TotalTime = pTime + pOpendayhours + pClosedayhours

End Function

I know there are some extra variables in here etc.

Please please please. Any help is very much appreciated.

Cheers,
Wade

 

[MichaelRed]

Use the fora "Advanced Search" function, keyword "faq181-261". THAT reference is to a faq which does the days calculation (also excludeds specified / enumerated Holidays). Others have embellished it to deal with the work day time issue. It would, perhaps, be better to start over with what you find throuogh the search than to continue with your present procedure.





MichaelRed
mlred@verizon.net