Ozzymandias
asked on
Calculate timelapse without weekend hours
hello all!
For a current assignment I need to calculate the amount of hours between two points in time (not too difficult), but keeping in mind that
- these two points can lie as much as a couple of months apart
- the hours between saturdays 06:00 and mondays 06:00 do not count
- these "weekend begin and end times" could be changed
- this is a VBA (Access) environment
I rated this question 100 points: should not be too difficult (eventhough I have no idea), but is rather urgent...
Any tips would be welcomed!
Ozzy
For a current assignment I need to calculate the amount of hours between two points in time (not too difficult), but keeping in mind that
- these two points can lie as much as a couple of months apart
- the hours between saturdays 06:00 and mondays 06:00 do not count
- these "weekend begin and end times" could be changed
- this is a VBA (Access) environment
I rated this question 100 points: should not be too difficult (eventhough I have no idea), but is rather urgent...
Any tips would be welcomed!
Ozzy
Dim lHours as long
dteFutureDate = whatever value
dteCurDay = dteFirstDate
Do until datediff("d", dteCurDay, dteFutureDate) = 0
if weekday(dtecurday) <> vbSaturday and weekday(dtecurday) <> vbSunday then
'replace 12 with the number of hours you need
'for the day if you are counting all 24, except
'for the few hours before 6:00am Monday then
'include the following commented out if statement
'if weekday(dtecurday) <> vbmonday then
lHours = lhours + 24
'else
' lhours = lhours + 18
'end if
end if
dtecurday = dateadd("d", 1, dtecurday)
doevents
loop
dteFutureDate = whatever value
dteCurDay = dteFirstDate
Do until datediff("d", dteCurDay, dteFutureDate) = 0
if weekday(dtecurday) <> vbSaturday and weekday(dtecurday) <> vbSunday then
'replace 12 with the number of hours you need
'for the day if you are counting all 24, except
'for the few hours before 6:00am Monday then
'include the following commented out if statement
'if weekday(dtecurday) <> vbmonday then
lHours = lhours + 24
'else
' lhours = lhours + 18
'end if
end if
dtecurday = dateadd("d", 1, dtecurday)
doevents
loop
The above code will count everything except for the weekends. Taking out the comments around the commented if statement will also not count the four hours before 6:00am.
If you need help altering it to what you are asking about, just let me know, but the above should give you enough info to get there on your own.
If you need help altering it to what you are asking about, just let me know, but the above should give you enough info to get there on your own.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
R2DTOEA,
thanx for the quick response. In the mean time I have been able to solve the problem. Your answer did point me somewhat in the right direction, but the thing is that the begin and end time of a work week are retreived from a GlobalOptions table, in the format "Monday 06:00" to "Saturday 06:00".
So I had some trouble calculating the number of hours between these two points. Doing the math in your head for this is easy, but in Access... Here's how I did it:
Public Function DetermineWeekendHours(strW eekBegin As String, strWeekEnd As String) As Double
Dim dayBegin, hrsBegin As String 'Split up strWeekBegin in days and hrs
Dim dayEnd, hrsEnd As String 'Split up van strWeekEnd in days and hrs
Dim datNxtWeekBegin As Date 'Date next "begin workweek"
Dim datNxtWeekEnd As Date 'Date next "end workweek"
Dim i As Integer
dayBegin = Left(strWeekBegin, InStr(strWeekBegin, " ") - 1)
hrsBegin = Mid(strWeekBegin, InStr(strWeekBegin, " ") + 1)
dayEnd = Left(strWeekEnd, InStr(strWeekEnd, " ") - 1)
hrsEnd = Mid(strWeekEnd, InStr(strWeekEnd, " ") + 1)
For i = 1 To 7 Step 1
If WeekdayName(Weekday(DateAd d("d", i, Now()), vbMonday), False, vbMonday) = dayEnd Then
datNxtWeekEnd = Format(DateAdd("d", i, Now()), "medium date")
Exit For
End If
Next i
For i = 1 To 7 Step 1
If WeekdayName(Weekday(DateAd d("d", i, datNxtWeekEnd), vbMonday), False, vbMonday) = dayBegin Then
datNxtWeekBegin = Format(DateAdd("d", i, datNxtWeekEnd), "medium date")
Exit For
End If
Next i
DetermineWeekendHours = (DateDiff("d", datNxtWeekEnd, datNxtWeekBegin, vbMonday) * 24) + _
(DateDiff("n", CDate(hrsEnd), CDate(hrsBegin)) / 60)
End Function
The rest was rather simple : determine the total number of hours between the two points in time, calculate the number of weekends between the two, and substract the number of WeekendHours from the total.
So, thanx again for your tips!
Ozzy
thanx for the quick response. In the mean time I have been able to solve the problem. Your answer did point me somewhat in the right direction, but the thing is that the begin and end time of a work week are retreived from a GlobalOptions table, in the format "Monday 06:00" to "Saturday 06:00".
So I had some trouble calculating the number of hours between these two points. Doing the math in your head for this is easy, but in Access... Here's how I did it:
Public Function DetermineWeekendHours(strW
Dim dayBegin, hrsBegin As String 'Split up strWeekBegin in days and hrs
Dim dayEnd, hrsEnd As String 'Split up van strWeekEnd in days and hrs
Dim datNxtWeekBegin As Date 'Date next "begin workweek"
Dim datNxtWeekEnd As Date 'Date next "end workweek"
Dim i As Integer
dayBegin = Left(strWeekBegin, InStr(strWeekBegin, " ") - 1)
hrsBegin = Mid(strWeekBegin, InStr(strWeekBegin, " ") + 1)
dayEnd = Left(strWeekEnd, InStr(strWeekEnd, " ") - 1)
hrsEnd = Mid(strWeekEnd, InStr(strWeekEnd, " ") + 1)
For i = 1 To 7 Step 1
If WeekdayName(Weekday(DateAd
datNxtWeekEnd = Format(DateAdd("d", i, Now()), "medium date")
Exit For
End If
Next i
For i = 1 To 7 Step 1
If WeekdayName(Weekday(DateAd
datNxtWeekBegin = Format(DateAdd("d", i, datNxtWeekEnd), "medium date")
Exit For
End If
Next i
DetermineWeekendHours = (DateDiff("d", datNxtWeekEnd, datNxtWeekBegin, vbMonday) * 24) + _
(DateDiff("n", CDate(hrsEnd), CDate(hrsBegin)) / 60)
End Function
The rest was rather simple : determine the total number of hours between the two points in time, calculate the number of weekends between the two, and substract the number of WeekendHours from the total.
So, thanx again for your tips!
Ozzy
Do you still need the answer to this or have you missed the deadline?
*