INF: How to Display Intervals Greater Than 24 Hours
PSS ID Number: Q109320
Article last modified on 07-31-1994

1.00 1.10 2.00

WINDOWS


---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access versions 1.0, 1.1, and 2.0
---------------------------------------------------------------------

SUMMARY
=======

When you are displaying an interval of greater than 24 hours in a
calculated control, the interval will not be displayed correctly.
Depending on the format of the control, you will see just the hours and
minutes, or you will see an incorrect date, such as 12/31/1889.

This article demonstrates three sample functions you can add to a
calculated control to correctly display intervals of greater than 24
hours.

MORE INFORMATION
================

NOTE: This article assumes that you are familiar with Access Basic and with
creating Microsoft Access applications using the programming tools provided
with Microsoft Access.

To find the number of hours elapsed between two dates, you can subtract
the values in Access Basic. For example, the sample code

   StartDate=#6/1/93 8:00AM#
   EndDate=#6/1/93 1:00PM#
   ?Format(EndDate-StartDate,"hh:mm")

will return a value of 05:00, which is the correct result.

However, the sample code

   StartDate=#6/1/93 8:00AM#
   EndDate=#6/3/93 3:00PM#
   ? Format(EndDate-StartDate,"hh:mm")

will return a value of 07:00, which is incorrect. The correct result
is 55:00.

The following three sample functions will display the elapsed time
correctly:

NOTE: In the following sample code, an underscore (_) is used as a line-
continuation character. Remove the underscore when re-creating this code in
Access Basic.

1. Open a module and enter the following in the Declarations section:

      Option Explicit
      Global StartDate
      Global EndDate

2. Enter the following functions:

      '---------------------------------------------------
      ' This function accepts a time interval and returns
      ' a value, such as "55:23" for 55 hours and 23 minutes.
      '---------------------------------------------------
      Function HoursMinutes (Interval)
         Dim Hours As Long, Minutes As Integer
         If IsNull(Interval) Then
            HoursMinutes = Null
         Else
            Hours = Int(Interval * 24)
            Minutes = CInt((Interval * 24 - Hours) * 60)
            If Minutes = 60 Then
               Minutes = 0
               Hours = hours + 1
            End If
            HoursMinutes = Hours & ":" & Format(Minutes, "00")
         End If
      End Function

      '---------------------------------------------------
      ' This function accepts a time interval and returns
      ' a value, such as "1 Day 23 Hours."
      '---------------------------------------------------
      Function DaysHours (Interval)
         Dim Days As Long, Hours As Long
         If IsNull(Interval) Then
            DaysHours = Null
         Else
            Days = Int(Interval)
            Hours = CInt((Interval - Days) * 24)
            If Hours = 24 Then
               Hours = 0
               Days = Days + 1
            End If
            DaysHours = Days & IIf(Days = 1, " Day ", " Days ") & _
               Hours & IIf(Hours = 1, " Hour", " Hours")
         End If
      End Function

      '---------------------------------------------------
      ' This function accepts a time interval and returns
      ' a value, such as "3 Days 17:43".
      '---------------------------------------------------
      Function DaysHoursMinutes (Interval)
         Dim Days As Long, Remainder As Double
         If IsNull(Interval) Then
            DaysHoursMinutes = Null
         Else
            Days = Int(Interval)
            Remainder = Interval - Days
            If Remainder > #23:59:59# Then
               Remainder = 0
               Days = Days + 1
            End If
            DaysHoursMinutes = Days & IIf(Days = 1, " Day ", " Days ") & _
               Format(Remainder, "hh:mm") & " Hours"
         End If
      End Function

3. Open a new module, and type the following in the module's Immediate
   window:

      StartDate=#6/1/93 8:00AM#
      EndDate=#6/3/93 3:05PM#
      ?HoursMinutes(EndDate-StartDate)

   The HoursMinutes() function will return a value of 55:05.

4. Type:

      ?DaysHours(EndDate-StartDate)

   The DaysHours() function will return the value "2 Days 7 Hours."

5. Type:

      ?DaysHoursMinutes(EndDate-StartDate)

   The DaysHoursMinutes() function will return the value "2 Days 07:05."

REFERENCES
==========

For more information on calculating time differences, query on the
following words here in the Microsoft Knowledge Base:

   calculate and timevalue

Additional reference words: 1.00 1.10 2.00
KBCategory:
KBSubcategory: PgmOthr

=============================================================================

Copyright Microsoft Corporation 1994.
