INFO: Sample Function to Format Date and Time Intervals

ID: Q186425

The information in this article applies to:

Microsoft Visual Basic for Applications version 5.0

SUMMARY

This article discusses how Visual Basic for Applications stores date and time intervals and provides a sample function to format them for output.

MORE INFORMATION

In Visual Basic for Applications, Date variables represent date and time values as a serial number. A serial number is the number of days from December 30, 1899, with the time being represented as a fraction of a day.

You can subtract two date/time values to get the interval between them. You can also add several interval values together to get a total elapsed time.

Because date/time formatting suppresses the date when it equals December 30, 1899, you can use the Format() function to display interval values less than 24 hours. For example:


   Debug.Print #3:00:00# + #4:00:00#

yields:

7:00:00AM

You can suppress the AM/PM suffix by using the Format() function with the appropriate mask. However, when the interval exceeds 24 hours, there is no simple method to get the correct output. If you don't use the Format() function, the value in excess of 24 hours will be displayed as a date. If you do use the Format() function, there is no mask available that will display both the number of days as an integer value while at the same time displaying the hour/minute/second component.

The following function formats a date/time value according to the mask provided. The masks are:


   MASK           SAMPLE DISPLAY (for 5 Days, 5hr, 15m, 45s)
   ------------   ------------------------------------------
   D H            5 Days 5 Hours
   D H:MM         5 Days 5:15
   D HH:MM        5 Days 05:15
   D H:MM:SS      5 Days 5:15:45
   D HH:MM:SS     5 Days 05:15:45
   H M            125 Hours 15 Minutes
   H:MM           125:15
   H:MM:SS        125:15:45
   M S            7515 Minutes 45 Seconds

You can easily extend the function to support additional masks.

NOTE: Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures.

Step-by-Step Example

In a new VBA project, add a Module.

Add the following code:


    Function FormatInterval (ByVal Interval As Date, _
                             ByVal Fmt As String) As String
      Dim Days As Long, Hours As Long, Minutes As Long, Seconds As Long
        Seconds = CLng(Interval * 86400)
        Minutes = Seconds \ 60
        Seconds = Seconds Mod 60
        Hours = Minutes \ 60
        Minutes = Minutes Mod 60
        Days = Hours \ 24
        Hours = Hours Mod 24
        Select Case Fmt
          Case "D H"
            FormatInterval = Days & IIf(Days <> 1, " Days ", " Day ") & _
                             Hours & IIf(Hours <> 1, " Hours", " Hour")
          Case "D H:MM"
            FormatInterval = Days & IIf(Days <> 1, " Days ", " Day ") & _
                             Hours & ":" & Format$(Minutes, "00")
          Case "D HH:MM"
            FormatInterval = Days & IIf(Days <> 1, " Days ", " Day ") & _
                             Format$(Hours, "00") & ":" & _
                             Format$(Minutes, "00")
          Case "D H:MM:SS"
            FormatInterval = Days & IIf(Days <> 1, " Days ", " Day ") & _
                             Hours & ":" & Format$(Minutes, "00") & ":" & _
                             Format$(Seconds, "00")
          Case "D HH:MM:SS"
            FormatInterval = Days & IIf(Days <> 1, " Days ", " Day ") & _
                             Format$(Hours, "00") & ":" & _
                             Format$(Minutes, "00") & ":" & _
                             Format$(Seconds, "00")
          Case "H M"
            Hours = Hours + Days * 24
            FormatInterval = Hours & _
                             IIf(Hours <> 1, " Hours ", " Hour ") & _
                             Minutes & _
                             IIf(Minutes <> 1, " Minutes", " Minute")
          Case "H:MM"
            Hours = Hours + Days * 24
            FormatInterval = Hours & ":" & Format$(Minutes, "00")
          Case "H:MM:SS"
            Hours = Hours + Days * 24
            FormatInterval = Hours & ":" & Format$(Minutes, "00") & ":" & _
                             Format$(Seconds, "00")
          Case "M S"
            Minutes = Minutes + (Hours + Days * 24) * 60
            FormatInterval = Minutes & _
                             IIf(Minutes <> 1, " Minutes ", " Minute ") & _
                             Seconds & _
                             IIf(Seconds <> 1, " Seconds", " Second")
          Case Else
            FormatInterval = ""
        End Select
    End Function

Run the project, and then pause it.

From the Debug or Immediate window, type the following:
?FormatInterval(5.14, "D H:MM:SS")
to display:
5 Days 3:21:36

Additional query words: kbVBA kbDSupport kbdse kbNoKeyWord

Keywords :
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type : kbinfo