Convert long integer to time format

[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.

Your help will be appreciated.

Thanks
Lawlee

 

[Brendan Reynolds]

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

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

</quote>

 

[Allen Browne]

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")

 

[Lawlee]

Thank you Brendan for you help.

 

[Lawlee]

Thank you guys for you help.

 

[Van T. Dinh]

I answered this question from you 2 days ago in the "gettingstarted"
newsgroup.

Did you see my reply in "gettingstarted"?