Converting numbers to times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I imported data from a text file. The fields with time data (with data
displayed in military time - 4 digit in the text file) were imported to
Access as 3 or 4 digit numbers. Is there a way to convert these numbers to
a time format using expressions in the design view of a query. (I'm at a
Kindergarten level with Access so am limited in what I know how to do!)
Note: I ultimately want to do two things: 1. Calculate differences in
times from different fields (Difference in time when arrived and time when
left) 2. Know the hour that different events occurred so that I can create
a pivot table to calculate the number of occurrences at each hour.
Any help is much appreciated!!! Thanks so much.
 
You mantioned that you have 3 or 4 digits in the field

How can you tell, if you have three digits, such as 123, if it is 12:03 or
1:23

Is there a rule to the numbers, such as minutes always 2 digits
 
Access has a data type to hold "place-in-time" values. This can be
date-only, time-only, or date/time. However, this is NOT a field type for
duration.

It isn't clear from your post whether you have duration data (the event took
3 minutes) or point-in-time date (the event occurred on 11/5/2005 1:20:30
PM).
 
Thanks to you both for your quick replies.

I am working with Point In Time Data. If the time was 1:24 am, it shows as
124. If the time was 3:45 pm, it shows as 1545. I only have two digit
entries for from midnight til 1 am. Did I clarify appropriately?

Again, thanks.
 
I think the previous posters are not familiar with military time. It is
obvious that 123 would be 01:23.
The CDate function will convert the numbers to a time data type you can
store in your table. You will also have to use the Format function so the
receiving field will understand where the numbers go.
=Format(CDate(123),"00:00")
The results will be 01:23 and is now in a date format that can be stored in
a Date/Time field in your table.
 
The TimeValue expression in the other message worked perfectly so I did not
try the CDate function.
When I was trying to figure all this out myself yesterday, I tried some
functions that included "=" and I couldn't get them to work. I'm asuming
that these are to be used in individual cells in worksheets rather than in
the design view of a query (which is pretty much all I know how to do). Is
this correct?
 
I don't think I posted my last reply correctly. Paraphrasing...
This worked like a charm. Thanks Thanks Thanks!!!

John Spencer said:
I would try

TimeValue(Format([YourNumber],"00:00"))

This should work for you.


JoLeigh said:
I imported data from a text file. The fields with time data (with data
displayed in military time - 4 digit in the text file) were imported to
Access as 3 or 4 digit numbers. Is there a way to convert these numbers
to
a time format using expressions in the design view of a query. (I'm at a
Kindergarten level with Access so am limited in what I know how to do!)
Note: I ultimately want to do two things: 1. Calculate differences in
times from different fields (Difference in time when arrived and time when
left) 2. Know the hour that different events occurred so that I can
create
a pivot table to calculate the number of occurrences at each hour.
Any help is much appreciated!!! Thanks so much.
Click to expand...
Click to expand...
 
=Format(CDate(123),"00:00")
The results will be 01:23 and is now in a date format that can be stored in
a Date/Time field in your table.
Click to expand...

CDate(123) is midnight on 2 May 1900<g>. Maybe
CDate(Format(123, "0\:00"))
 
Actually, I had the same thought. But I tested the expression from Klatuu
and it seemed to work. At least it was returning a string that seemed to be
correct.

My personal preferences is your method or my method using TimeValue. Of
course, none of use tried to handle bad data in the field - such as null,
277, 2501.
 
Back
Top