Microsoft KB Archive/186425

= INFO: Sample Function to Format Date and Time Intervals =

Article ID: 186425

Article Last Modified on 10/20/2000

-

APPLIES TO


 * Microsoft Visual Basic for Applications 5.0

-



This article was previously published under Q186425



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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.