Importing a date and time from Excel

[Tony Williams]

I am trying to import a spreadsheet which has two columns which hold data as
a date and time like this
02/06/2006 17:26:36:2

I have tried importing the columns formatted as dates but I get a conversion
error and none of the data is imported. I realise that I could split the row
into 2 and then import them seperately but I would have to concatenate them
after the import. This is likely to be a regular import and the user wouldn't
have the knowledge of how to do that. Anyone any ideas how I can import the
data into a Date/Time field in Access 2003?
Thanks
Tony

 

[Jeff Boyce]

Tony

A bit of clarification ... are you saying you have two columns in Excel and
one holds dates, the other times? Or are you saying you have two columns,
and each column holds date AND time?

In Excel, is the actual string "02/06/2006 17:26:36:2" stored, or is this a
formatted display of some other value?

By the way, what's that trailing ":2" supposed to represent?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Hi Jeff, there are 2 columns which hold a date AND time in the format of my
previous post. The columns store the data exactly in the format
"02/06/2006 17:26:36:2" I'm not sure what the last "2" represents I assumed
it was something like the millisecond (?) value as it is different for each
cell eg in another cell the data is
01/09/2008 11:58:54:8

Hope that helps.

I'm in the UK and it's 19.59 here so I'm shutting my brain down now until
tomorrow so if I don't respond to you again tonight (today?) I'm not being
rude I'll check again tomorrow for any reply.
Thanks
Tony

 

[Jeff Boyce]

Tony

I'm still not clear whether the actual stored value is "01/09/2008
11:58:54:8" or that is just how Excel is formatting the value for display.

Try opening Excel, highlighting one of those date/time cells, and applying a
"Standard" format to it. What does it show?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Good morning jeff!

I've formatted a cell with the "General" format (I'm using Excel 2007 and
there isn't a "Standard" format) rather than a "Date" format and the data
display doesn't change it is as I've previously posted?

Any help?
Thanks for sticking with me.
Tony

 

[Jeff Boyce]

Now try formatting as text. I'm guessing that what's stored there is NOT a
date (it just plays one on TV), but a string of characters...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Hi Jeff
I think you're right. I formatted as Text and nothing changed. A further
clue I think is that the data is always ranged left no matter how I format
it. There are a couple of other columns with just dates and they are ranged
right. So I guess you're right, the data is just a string of characters even
though it looks like a date, that's what fooled me.

So I need to find someway of importing at least the date part into Access
and then see if I can format it in Access as a date. What do you think?

Thanks again for coming back to me.
Cheers
Tony

 

[Jeff Boyce]

Aside from that puzzling last couple of characters, I believe you could use
an Access function that converts a string-that-looks-like-a-date into an
actual date/time value.

(and I suspect those puzzling characters are the truncated version of what
originates as milliseconds, as you mentioned).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Thanks Jeff I'll post back if I have any luck.
Cheers and thanks again
Tony