INFO: Sample Function to Format Date and Time Intervals (186425)

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: 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

1. In a new VBA project, add a Module.
2. 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
   						

3. Run the project, and then pause it.
4. From the Debug or Immediate window, type the following:

   ?FormatInterval(5.14, "D H:MM:SS")

   to display:

   5 Days 3:21:36