With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.
Public Function fnMin(ParamArray ValList() As Variant) As Variant
Dim intLoop As Integer
Dim myVal As Variant
For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal
End Function
Public Function fnMax(ParamArray ValList() As Variant) As Variant
Dim intLoop As Integer
Dim myVal As Variant
For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) > myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMax = myVal
End Function
DateDiff('s',[date1],[date2]) AS Expr1
Public Function ISO_WorkTimeDiff( _
ByVal datDateTimeFrom As Date, _
ByVal datDateTimeTo As Date, _
Optional ByVal booNoHours As Boolean) _
As Long
' Purpose: Calculate number of working minutes between date/times datDateTimeFrom and datDateTimeTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Returns: "Working minutes". Divide by 60 to obtain working hours.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, Copenhagen
'
' If booNoHours is True, time values are ignored.
' Specify begin and end time of daily working hours.
Const cdatWorkTimeStart As Date = #8:00:00 AM#
Const cdatWorkTimeStop As Date = #6:00:00 PM#
Const cbytWorkdaysOfWeek As Byte = 5
Dim bytSunday As Byte
Dim intWeekdayDateFrom As Integer
Dim intWeekdayDateTo As Integer
Dim datTimeFrom As Date
Dim datTimeTo As Date
Dim lngDays As Long
Dim lngMinutes As Long
Dim lngWorkMinutesDaily As Long
' No special error handling.
On Error Resume Next
If DateDiff("n", datDateTimeFrom, datDateTimeTo) <= 0 Then
' Nothing to do. Return zero.
Else
' Calculate number of daily "working minutes".
lngWorkMinutesDaily = DateDiff("n", cdatWorkTimeStart, cdatWorkTimeStop)
' Find ISO weekday for Sunday.
bytSunday = WeekDay(vbSunday, vbMonday)
' Find weekdays for the dates.
intWeekdayDateFrom = WeekDay(datDateTimeFrom, vbMonday)
intWeekdayDateTo = WeekDay(datDateTimeTo, vbMonday)
' Compensate weekdays' value for non-working days (weekends).
intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
' Calculate number of working days between the weeks of the two dates.
lngDays = (cbytWorkdaysOfWeek * DateDiff("w", datDateTimeFrom, datDateTimeTo, vbMonday, vbFirstFourDays))
' Add number of working days between the two weekdays, ignoring number of weeks.
lngDays = lngDays + intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
If Not booNoHours = True Then
' Extract begin and stop hour (time) for the working period.
datTimeFrom = TimeSerial(Hour(datDateTimeFrom), Minute(datDateTimeFrom), Second(datDateTimeFrom))
datTimeTo = TimeSerial(Hour(datDateTimeTo), Minute(datDateTimeTo), Second(datDateTimeTo))
' Adjust times before or after daily working hours to boundaries of working hours.
If DateDiff("n", datTimeFrom, cdatWorkTimeStart) > 0 Then
datTimeFrom = cdatWorkTimeStart
ElseIf DateDiff("n", datTimeFrom, cdatWorkTimeStop) < 0 Then
datTimeFrom = cdatWorkTimeStop
End If
If DateDiff("n", datTimeTo, cdatWorkTimeStart) > 0 Then
datTimeTo = cdatWorkTimeStart
ElseIf DateDiff("n", datTimeTo, cdatWorkTimeStop) < 0 Then
datTimeTo = cdatWorkTimeStop
End If
' Calculate number of working minutes between the two days, ignoring number of days.
lngMinutes = DateDiff("n", datTimeFrom, datTimeTo)
End If
' Calculate number of working minutes between the two days using the workday count.
lngMinutes = lngMinutes + (lngDays * lngWorkMinutesDaily)
End If
ISO_WorkTimeDiff = lngMinutes
End Function
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.