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

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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
Click to expand...

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.
 
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
 
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/
 
Thanks Ken I'll have go at that.
Thanks
Tony

Ken Snell (MVP) said:
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/
Click to expand...
 
Back
Top