Convert long integer to time format

  • Thread starter Thread starter Lawlee
  • Start date Start date
L

Lawlee

Hi there,

I have a field with time in long integer format eg. 95505 and 154105.
How can I get it into hh:mm:ss AM/PM format?
From 95505 to 9:55:05 AM and 154105 to 3:41:05 PM.
Click to expand...

Your help will be appreciated.

Thanks
Lawlee
 
I answered your question on the 11th, in
microsoft.public.access.externaldata ...

<quote>
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

--
Brendan Reynolds


Lawlee said:
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
Click to expand...
</quote>
 
Create a query into this table.

In the Field row of query design, enter:
TimeValue(Format([Field1], "00\:00\:00"))
replacing Field1 with the name of your field.

The result will the the am/pm format, if that is how your have time formats
set in the Windows Control Panel under Regional Settings. Otherwise (and
assuming this is for display only, not for time calculations), you could
force it with:
Format(TimeValue(Format([Field1], "00\:00\:00")), "h:nn:ss AMPM")
 
I answered this question from you 2 days ago in the "gettingstarted"
newsgroup.

Did you see my reply in "gettingstarted"?
 
Back
Top