Converting a column of text from Excel into a date for import to A

[Tony Williams]

I have a spreadsheet that has a text formtted column which holds a date as in
1192009. How do I turn this text into 11/09/2009 as a date?
Thanks
Tony

 

[pietlinden]

I have a spreadsheet that has a text formtted column which holds a date as in
1192009. How do I turn this text into 11/09/2009 as a date?
Thanks
Tony

This could get ugly very fast.
How do you know which is a month and which is a day with data like
this:

1152009
Is that 11/5/2009 or 1/15/2009?

The rest is trivial.
use Mid(), Left(), Right() to extract parts of the date and DateSerial
to put them together.

But you need to solve the ambiguity problem first.

 

[Tony Williams]

Hi Piet.
I'm in the UK and am told that the format will always be mm/dd/yyyy.

I've not used the expressions you mentioned, could you help me out with how
I would import the text and then reformat it using these expressions?

Thanks
Tony

 

[Ken Snell \(MVP\)]

If you import the date value "as is", that is, as yyyyddmm, then you can
convert that value into a valid date value with an expression similar to
this:

DateSerial(CInt(Left(AsIsDateField, 4)), CInt(Right(AsIsDateField, 2)),
CInt(Mid(AsIsDateField, 5, 2)))

Note that the result from the above expression is a valid ACCESS date; you
then can display it in whatever format you want (mm/dd/yyyy or dd/mm/yyyy).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Tony Williams]

Thanks Ken I'll have go at that.
Thanks
Tony

If you import the date value "as is", that is, as yyyyddmm, then you can
convert that value into a valid date value with an expression similar to
this:

DateSerial(CInt(Left(AsIsDateField, 4)), CInt(Right(AsIsDateField, 2)),
CInt(Mid(AsIsDateField, 5, 2)))

Note that the result from the above expression is a valid ACCESS date; you
then can display it in whatever format you want (mm/dd/yyyy or dd/mm/yyyy).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/