Time Value Formatting Beyond Twenty Four Hours

Introduction.

The standard time format hh:nn:ss displays values up to 23 hours, 59 minutes, and 59 seconds (23:59:59). After that, it restarts at 00:00:01 one second past midnight (00:00:00) on the next day. Using the built-in Format() function, it is not possible to display time values beyond 24 hours, such as 45:30:15 (45 hours, 30 minutes, 15 seconds).

So, how do we format time values that exceed twenty-four hours? This requirement often arises in practical applications, such as calculating an employee’s total hours worked in a week (40:30:15), or an IT firm employee’s total internet browsing time for a month (115:47:12) extracted from server logs.

To achieve this, we need a custom function. The following VBA function accepts time values in a suitable format and produces the desired output. You can copy and paste the code into a Standard Module in your project and use it wherever extended time formatting is required. The FormatTime() Function Code.

Public Function FormatTime(ByVal StartDateTime As Variant, Optional ByVal EndDateTime As Variant = CDate(0)) As String
Dim v1 As Double, hh As Integer, nn As Integer, ss As Integer
'-------------------------------------------------------------------------
'Remarks: Time-Format function for time value beyond 23:59:59
'Author : a.p.r.pillai
'Date   : August-2016
'Rights : All Rights Reserved by www.msaccesstips.com
'
'Input options
'1. StartDatetime & EndDateTime (both date/time values or both Time Values)
'2. StartDateTime = Date/Time Value and EndDateTime value=0
'3. StartDateTime = Time Value in decimal format – e.g. 0.999988425925926) for TimeValue("23:59:59") or less
'4. StartDateTime = Time Value in Seconds – e.g. 86399 for Timevalue("23:59:59")*86400
'-------------------------------------------------------------------------
'VALIDATION SECTION

'If Input Value is in whole seconds (TimeValue*86400)
'for applications like internet Server log time etc.
'Option4

On Error GoTo FormatTime_Err

If TypeName(StartDateTime) = "Long" Then
   GoTo Method2
End If

If EndDateTime > 0 And EndDateTime < StartDateTime Then
'End-Date is less than Start-Date
   MsgBox "Error: End-Date < Start Date - Invalid", vbCritical, "Format_Time()"
ElseIf (EndDateTime > StartDateTime) And (StartDateTime > 0) Then
'option 1
   If TypeName(StartDateTime) = "Date" And TypeName(EndDateTime) = "Date" Then
     v1 = EndDateTime - StartDateTime
     GoTo Method1
   End If
ElseIf StartDateTime > 0 And EndDateTime = 0 Then
'option 2
'Is it Today's date & Time
   If Int(StartDateTime) = Int(Now()) Then
      'Remove Date number and take only time value
      v1 = StartDateTime - Int(StartDateTime)
   Else
   'Option 3
      'Assumes Value is Time-Value in decimal format
      v1 = StartDateTime
   End If
End If

'Option 1 to 3
Method1:
hh = Int(v1 * 24)
nn = Int((v1 - hh / 24) * 1440)
ss = Round((v1 - (hh / 24 + nn / 1440)) * 86400, 0)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")
Exit Function

'Time Input in Seconds
'Option 4
Method2:
v1 = StartDateTime
hh = Int(v1 / 3600)
nn = Int((v1 - (hh * 3600#)) / 60)
ss = v1 - (hh * 3600# + nn * 60)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")

FormatTime_Exit:
Exit Function

FormatTime_Err:
MsgBox "Error: " & Err & " - " & Err.Description, , "FormatTime()"
Resume FormatTime_Exit

End Function

Trial Run of Code.

Let’s go through a few examples to understand how to correctly pass parameter values to the FormatTime() function. You can try the following sample runs by typing them directly into the VBA Immediate (Debug) Window.

The FormatTime() function accepts either a Date & Time value or a Time value as its primary parameter. The second parameter is optional. The following rules apply when parameter values are passed to the function:

1. When both parameters are entered both should be either Date/Time Values or both Time Values only.  The difference in time will be calculated by subtracting the first parameter value from the second parameter.

2. The second parameter is optional. If omitted, then the Date value will be ignored when the Date/Time value is passed in the first parameter. The time value will be formatted and displayed.

3. The first parameter can be a time value in decimal format (e.g., 0.999988425925926 ). Omit the second parameter.

4. The first parameter is acceptable as a time value in Seconds (e.g. 86399). Omit the second parameter.

Example-1:

Both Parameter values are Date/Time Values. The second Parameter value should be greater than the first parameter value.

StartDT = DateAdd("d",-2,Now()) = 8/27/2016 7:38:22 PM 

EndDT = Now() = 8/29/2016 7:41:13 PM

? FormatTime(StartDT,EndDT)

Result: 48:02:51

Example-2:

First parameter Date/Time Value, second parameter is optional and omitted.

SDateTime=Now() or DateVallue("08/29/2016")+TimeValue("18:30:15")

? FormatTime(SDateTime)

Result: 18:30:15

Example-3:

When the first parameter is passed as a numeric time value (for example, 2.00197916667094), the FormatTime() function interprets it correctly. In this case, the result will be 48:02:51.

This decimal value represents the difference between two Date/Time values (End Date/Time minus Start Date/Time). The result is expressed as a decimal number, where:

• The whole number represents the number of days (e.g., 2 days × 24 hours = 48 hours).

• The fractional part represents the time of day, converted into Hours:Minutes:Seconds.

Such time values often appear as the result of summarizing multiple records from a Table or Query, where the difference between start and end times is accumulated.

StartDT = 2.00197916667094 

? FormatTime(StartDT)

Result: 48:02:51

The whole number 2 on the left side of the decimal point is the number of days and it will be ignored, when the second parameter is omitted.

You can create a time number similar to the one above for testing this function. To do that we will look at a few basics on the time value to understand them better.

1 Day = 24 hours. 1 Hour = 60 Minutes. 1 Minute = 60 Seconds. So 1 Day = 24 * 60 * 60 = 86400 Seconds.

1 second before mid-night = 86400-1 = 86399 seconds = 23:59:59

To convert the time 23:59:59 (86399) into the internal representation of time value, divide 86400 by 86399 (86399/86400). Result: 0.999988425925926. The time 23:59:59 is stored in computer memory in this form. When combined with the current Date-number, it will be like 42612.999988425925926 for 08/30/2016 23:59:59

Let us input this time number alone into our function and try out the result.

? FormatTime(0.999988425925926)

Result: 23:59:59

Example-4:

So, if you want to convert a Time number into Seconds, then multiply it by 86400. 0.999988425925926 * 86400 = 86399

You can input seconds as a time number as the first parameter to get it formatted in Hours:Minutes:Seconds.

? FormatTime(86399)

Result: 23:59:59

If you want larger values for testing this Function, try the following examples.

SD = 86399+86400+86000
   = 258799 seconds

SD = SD/86400
   = 2.9953587962963 time number

? FormatTime(SD)

Result: 71:53:19

Now, let us try passing the time value directly in seconds.
(For example, assume this value represents the total internet browsing time summarized in seconds.)

When a time value is provided in seconds, the FormatTime() function converts it into the proper format of Hours:Minutes:Seconds. This is particularly useful when server logs or other systems record usage duration in raw seconds, and we want to present it in a more readable form, such as 115:47:12.

SD= 258799

? FormatTime(SD)

Result: 71:53:19

If you maintain a library database, you can move this function there so that you don’t need to copy the code into every other database.

A library database is a common repository containing your custom functions, modules, or wizards that can be referenced by other projects. Using this approach allows you to reuse functions across multiple databases without duplicating code. For a detailed discussion on this topic, see the page MS-Access and Reference Library.

We welcome your comments or suggestions for improving the FormatTime() function. Please leave them in the Comments Section at the end of this post.

• Sum, Min, Max, Avg, ParamArray
• Useful Report Functions
• Cardinal Text Format in Access
• Auto-Numbering in Query Column