# Thread: Convert Number to Time Value

1. Registered User
Join Date
Apr 2004
Posts
173

## Answered: Convert Number to Time Value

I have the following types of values:

11
2011
92011
192011

They are numbers but they indicate time. The first value is 11 seconds after midnight 12:00:11, The second is 12:20:11, the third is 9:20:11 (AM), and the fourth is 7:20:11 (PM). How can I convert these types of values quickly in Access? They don't have a consistent number of digits. I've done this in excel in the past by separating the values into each group of 2 digits you'd expect to see in a time value using the int and mod functions. Is there an easier way that I'm overlooking?

Thanks.

## " Originally Posted by mmbosman I have the following types of values: 11 2011 92011 192011 They are numbers but they indicate time. The first value is 11 seconds after midnight 12:00:11, The second is 12:20:11, the third is 9:20:11 (AM), and the fourth is 7:20:11 (PM). How can I convert these types of values quickly in Access? They don't have a consistent number of digits. I've done this in excel in the past by separating the values into each group of 2 digits you'd expect to see in a time value using the int and mod functions. Is there an easier way that I'm overlooking? Thanks. Try this: Code: `timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00"))` Jeff"

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Write a function that convets the number inyo a datetime value.

4. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
630
Parse it into a date field

= hr & ":" & ":"& min & ":" & sec

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
There more than on way to skin a cat

Code:
```Sub TESTIT()
Debug.Print TimeIt("11")
Debug.Print TimeIt("2011")
Debug.Print TimeIt("92011")
Debug.Print TimeIt("192011")

End Sub

Function TimeIt(ThisTime)

Select Case Len(ThisTime)

Case 2
TimeIt = "12:00:" & ThisTime
Case 4
TimeIt = "12:" & Mid(ThisTime, 1, 2) & ":" & Mid(ThisTime, 3, 2)
Case 5
TimeIt = Mid(ThisTime, 1, 1) & ":" & Mid(ThisTime, 2, 2) & ":" & Mid(ThisTime, 4, 2)
Case 6
TimeIt = Val(Mid(ThisTime, 1, 2)) - 12 & ":" & Mid(ThisTime, 3, 2) & ":" & Mid(ThisTime, 5, 2)

End Select

End Function```
then in a query

realtime:timeit([feildnamethathasthedata])
Last edited by myle; 08-24-15 at 15:51. Reason: spelling

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
If the time is 12 seconds AFTER midnight then presumably it should be 00:00:12 NOT 12:00:12?
Myle's code does the job, as you would expect
But fir me Id want to return a datetime value which could then be formatted as required in the data consumer / presentation layer, whilst retaining the ability to use date time functions for aggregation.

As a refinement id suggest instead of looking for an exact length you look at 3 cases <=2, <=4 & <=6 You probably need to make the function more robust to handle values out of range eg 7 or more digits, no digits, alphanumerics, nulls etc...

But thats polishing what already meets the OP's stated requirements
Last edited by healdem; 08-25-15 at 06:02.

7. Registered User
Join Date
Aug 2015
Posts
3
Originally Posted by mmbosman
I have the following types of values:

11
2011
92011
192011

They are numbers but they indicate time. The first value is 11 seconds after midnight 12:00:11, The second is 12:20:11, the third is 9:20:11 (AM), and the fourth is 7:20:11 (PM). How can I convert these types of values quickly in Access? They don't have a consistent number of digits. I've done this in excel in the past by separating the values into each group of 2 digits you'd expect to see in a time value using the int and mod functions. Is there an easier way that I'm overlooking?

Thanks.
Try this:

Code:
`timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00"))`
Jeff

8. Registered User
Join Date
Apr 2004
Posts
173
Thanks so much for your help with the multiple answers. The timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00")) did the trick and was the easiest to implement. Thanks again.

9. Registered User
Join Date
Aug 2015
Posts
3