How to convert a long integer number to time format

[Lawlee]

Hi there,

I'm struggling to convert long integer into time format.
The data is mixed and in the following 2 formats 141030 and 91017.
Some of the time has 5 and the others 6 numbers.

So i'm trying to get both into the following format 15:10:03 PM-
hh:mm:ss AM/PM

How can i do this?

Thank you
Lawlee

 

[Brendan Reynolds]

Public Function NumToTime(num As Long) As String

'141030
'91017

Dim strWork As String

strWork = CStr(num)
If Len(strWork) = 6 Then
strWork = Left$(strWork, 2) & ":" & Mid$(strWork, 3, 2) & ":" &
Right$(strWork, 2)
Else
strWork = Left$(strWork, 1) & ":" & Mid$(strWork, 2, 2) & ":" &
Right$(strWork, 2)
End If

NumToTime = Format$(strWork, "hh:mm:ss AM/PM")

End Function

Examples of use, in the Immediate window ...

? numtotime(141030)
02:10:30 PM
? numtotime(91017)
09:10:17 AM

 

[Lawlee]

Thanks Brendan