time conversion from text

[Matilda]

Hi All,

I am importing from a text file, and the date field and time field are
separate.
Each consists of unseparated digits, 8 date and 4 time. The import wizard
handles the date ok but there is no allowing for the separate time text field
conversion. I have tried everything but to no avail. I have 5 time fields in
each file I need to import, around 100 files. I was about to embark on a
process of separating the digits, inserting a colon, then re-importing, but
thought there might be a faster solution. Is there?

Many TIA's

Matilda

 

[John W. Vinson/MVP]

Hi All,

I am importing from a text file, and the date field and time field are
separate.
Each consists of unseparated digits, 8 date and 4 time. The import wizard
handles the date ok but there is no allowing for the separate time text
field
conversion. I have tried everything but to no avail. I have 5 time fields
in
each file I need to import, around 100 files. I was about to embark on a
process of separating the digits, inserting a colon, then re-importing,
but
thought there might be a faster solution. Is there?

Assuming that 0930 (930 as an integer) means 9:30am, and 1550 means 3:50pm,
then
TimeSerial([timefield] \ 100, [timefield] MOD 100, 0)
will do the trick. The backslash is not a typo, it's the integer division
operator.

 

[Matilda]

Thanks, John !!!

I tried using this formula in Excel:
=CONCATENATE([date cell]," ",LEFT(time cell,2),":",RIGHT(time cell,2))

which gave me a datetime field 39457 09:35 for two fields originally
10/01/2008 0935
I inserted this into a new column for the five time fields, deleted the
unnecessary old fields and saved ready to import into Access, and format as
dd/mm/yyyy hh:mm

phew!! for only a hundred or so files.

I write this for anybody else in the same situation, becuase your elegant
solution works a **treat** and saves all that manipulation - and time.

Thanks a heap.

Matilda

Hi All,

I am importing from a text file, and the date field and time field are
separate.
Each consists of unseparated digits, 8 date and 4 time. The import wizard
handles the date ok but there is no allowing for the separate time text
field
conversion. I have tried everything but to no avail. I have 5 time fields
in
each file I need to import, around 100 files. I was about to embark on a
process of separating the digits, inserting a colon, then re-importing,
but
thought there might be a faster solution. Is there?

Assuming that 0930 (930 as an integer) means 9:30am, and 1550 means 3:50pm,
then
TimeSerial([timefield] \ 100, [timefield] MOD 100, 0)
will do the trick. The backslash is not a typo, it's the integer division
operator.

 

[John W. Vinson]

which gave me a datetime field 39457 09:35 for two fields originally
10/01/2008 0935

CDate([textdate]) + TimeSerial([timefield] \ 100, [timefield] MOD 100, 0)

will do both fields in one step.