CSV text to Date Format

[Stacey]

I have a file which I will be uploading into Access on a regular basis. The
date field is formatted to text which I would like to format into a date
field upon importing into Access. I would also like to rearrange the date
into a regular format, it reads as such 082101 and I would like it to read
01/21/08. Any help is greatly appreciated. Thanks in advance!

Stacey.

 

[John W. Vinson]

I have a file which I will be uploading into Access on a regular basis. The
date field is formatted to text which I would like to format into a date
field upon importing into Access. I would also like to rearrange the date
into a regular format, it reads as such 082101 and I would like it to read
01/21/08. Any help is greatly appreciated. Thanks in advance!

Stacey.

That's a VERY strange date convention: two digit year, then two digit DAY,
then two digit month? Are you certain that yesterday wouldn't have been
080121?

If it is as you post, then try

DateSerial(Left([textdate], 2), Right([textdate], 2), Mid([textdate], 3, 2))

If it is as I propose, swap places between the Right() and Mid() function
calls.

John W. Vinson [MVP]

 

[Stacey]

John, it worked. Thanks!!

I have a file which I will be uploading into Access on a regular basis. The
date field is formatted to text which I would like to format into a date
field upon importing into Access. I would also like to rearrange the date
into a regular format, it reads as such 082101 and I would like it to read
01/21/08. Any help is greatly appreciated. Thanks in advance!

Stacey.

That's a VERY strange date convention: two digit year, then two digit DAY,
then two digit month? Are you certain that yesterday wouldn't have been
080121?

If it is as you post, then try

DateSerial(Left([textdate], 2), Right([textdate], 2), Mid([textdate], 3, 2))

If it is as I propose, swap places between the Right() and Mid() function
calls.

John W. Vinson [MVP]