Show the time in hh:mm.sss format using VBA

In this article, we will create a macro to format the time in hh:mm.sss format.

Raw data for this example consists of certain time values in column E.

ArrowSampleData

In this article, we have created a user defined function (or custom function) “HHMMSSSFormat”. This function takes date type as input and returns output in string data type in hh:mm.sss format.

Logic explanation

In “HHMMSSSFormat” function to convert 60 seconds to a three digit number, we have divided seconds in the defined time value by 60 to get fraction for defined seconds and then multiplied it by thousand to get the three digit number.

“HHMMSSSFormat” function can be used by calling either directly in the Excel sheet or by using the function inside the other procedure (or macro).

The image below shows how we have used “HHMMSSSFormat” function in the Excel sheet to derive the time in hh:mm.sss format.

ArrowCustomFunction

We have also created a “GettingCurrentTimeinHHMMSSSFormat” macro which uses the “HHMMSSSFormat” function to display the current time in hh:mm.sss format in a message box. The image below shows the output when we run this macro at 3:54:30s.

ArrowMacroOutput

 

Please follow below for the code


Option Explicit

Function HHMMSSSFormat(DateTime As Date) As String
'function will return string value

'Declaring integer variable
Dim SecondValue As Integer

'Extracting seconds from DateTime parameter
SecondValue = Second(DateTime)

'Converting seconds value to three digit number
SecondValue = (SecondValue / 60) * 1000

'Change the formatting of time in the required format
HHMMSSSFormat = Format(Hour(DateTime), "00") & ":" & _
   Format(Minute(DateTime), "00") & "." & Format(SecondValue, "000")
   
End Function


Sub GettingCurrentTimeinHHMMSSSFormat()

'Declaring string variable
Dim CurrentTime As String

'Calling custom function HHMMSSSFormat
CurrentTime = HHMMSSSFormat(Now)

'Displaying message box with Ok button only
MsgBox CurrentTime, vbOKOnly, "Current Time"

End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube