Elapsed time between 2 dates

I need to calculate the elapsed time between two dates on my access form but I need it to compute just work days and work hours between these 2 dates. Is it possible to get the days, hours, & minutes between 2 dates and only return the workdays, work hours, & work minutes?
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
Gustav BrockCIOCommented:
You can use this function to format the output:
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  FormatHourMinute = strHourMinute
End Function

Open in new window


lngWorkMinutes = ISO_WorkTimeDiff(DateTimeFrom, DateTimeTo)
strHourMinute = FormatHourMinute(TimeSerial(lngWorkMinutes \ 60, lngWorkMinutes Mod 60, 0))

Gustav BrockCIOCommented:
Yes, you can use this function:
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 = #4: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.
    ' 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

Open in new window

It returns minutes you can format as you like.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.