Formatting Cumulative Times

As we’ve already stated, VBA has no way of storing, or measuring, elapsed times in its date/time fields. When you enter 8:30, you may think you’re entering the number of hours someone worked, but you’re actually entering a specific time: 8:30 a.m. on the date you entered the value. VBA has no qualms about performing aggregate calculations on date/time fields—they’re stored internally as floating-point values, so there’s no problem performing the calculation—but the result will not be what you had in mind.

The task, then, is to allow you to enter time values as you’ve become accustomed. You’ll need to convert them to some simple value for calculations and then format the output as a standard time value for display. To make all this happen, you’ll need the two functions included here, dhCMinutes and dhCTimeStr. dhCMinutes accepts a date/time value as a parameter and returns the time portion, converted to the corresponding number of minutes. Given that value, you can easily sum up a series of time values. Then, when you’re ready to display your sum, you’ll need the dhCTimeStr function. This one, given a number of minutes, returns a string representing the total, in hh:mm format.

For example, imagine you need to find the sum of 8:30, 12:30, and 13:25 (in each case, a span of time). To sum the three time values and convert that sum back into a time format, you could use an expression like this:

dhCTimeStr(dhCMinutes(#8:30#) + dhCMinutes(#12:30#) + _
 dhCMinutes(#13:25#))

The result of that expression would be the string “34:25”.

Each of the functions consists of just a few lines of code. The dhCMinutes function, shown in Listing 2.26, uses the TimeValue function to extract the time portion of the date, and multiplies the resulting fraction by 24*60, resulting in the number of minutes represented by the fractional portion.

Listing 2.26: Convert a Date/Time Value into Elapsed Minutes

Function dhCMinutes (dtmTime As Date) As Long
    ' Convert a date/time value to the number of
    ' minutes since midnight (that is, remove the date
    ' portion, and just work with the time part). The
    ' return value can be used to calculate sums of
    ' elapsed time.
    ' Convert from a fraction of a day to minutes.
    dhCMinutes = TimeValue(dtmTime) * 24 * 60
End Function

The function that converts the number of minutes back to a string formatted as a time value, dhCTimeStr (Listing 2.27), is just as simple. It takes the number of minutes and performs an integer division (using the \ operator) to get the number of hours. Then it uses the Mod operator to find the number of minutes (the remainder when you divide by 60). The function formats each of those values and concatenates them as a string return value.

Listing 2.27: Convert Elapsed Minutes into a Formatted String

Function dhCTimeStr (lngMinutes As Long) As String
    ' Convert from a number of minutes to a string
    ' that looks like a time value.
    '
    CTimeStr = Format(plngMinutes \ 60, "0") & _
     GetTimeDelimiter() & Format(lngMinutes Mod 60, "00")
End Function

There’s just one small wrinkle here: not everyone uses the same time delimiter character. The built-in VBA formatting specifiers take that into account, but in this case, you’re supplying your own formatting. The solution is to ask Windows for the local time delimiter, of course. The problem is that there’s no simple way to retrieve this information, even in 32-bit versions of Windows. The simplest method is just to pull the information from WIN.INI, as you might have done in Windows 3.1! Although you can retrieve the information directly from the Registry, that requires much more work. You can also request the information using the Windows API, but that, too, requires many more steps.

Therefore, the function GetTimeDelimiter (Listing 2.28) digs into WIN.INI using the GetProfileString API call. This function retrieves the sTime setting in the [intl] section of the file, so any function needing to format time values can use the native delimiter.

Listing 2.28: Retrieve the Local Time Delimiter

Private Function GetTimeDelimiter() As String
    ' Retrieve the time delimiter from, believe it or not,
    ' WIN.INI. This is the only reasonable solution
    ' to this problem, even in this day and age!
    Const conMaxSize = 10
    Dim strBuffer As String
    Dim intLen As Integer
    strBuffer = Space(conMaxSize)
    intLen = GetProfileString("intl", "sTime", "", strBuffer, _
     conMaxSize)
    GetTimeDelimiter = Left(strBuffer, intLen)
End Function

What? Using WIN.INI to retrieve values that ought to be in the Registry? Yes, it’s true. The problem is that the Registry doesn’t provide a one-step way to retrieve this information. You must first determine the locale ID and then find the necessary information. Because Windows guarantees that it maintains this information in WIN.INI (or in the associated Registry keys, under Windows NT), there’s no reason not to do this the simple way, using GetProfileString.