Need to Convert String to m/d/yyyy Format

[tiger6115]

I am looking to automate an import of an excel worksheet provided by an insurance company. Unfortunately the date fields on the worksheets are not formatted correctly. The dates are displayed as 9192018 instead of 9/19/2018. I have tried to format the dates while importing in access but it converts the dates into nonsense (generic 12PM Time, 1499 dates, etc.) I have also tried to format the dates in excel prior to importing them in Access but that doesn't work either (It shows nothing but ##### and the error says "dates and times that are negative....").

Is there a way to format all date values that are mddyyyy to m/dd/yyyy automatically while importing to access? Thank you in advance for any help/direction you can provide.


A few examples of the dates:

PolExpDt

1012019
12312018
1012019
9012018
9182018
10042018
11202018
9012018
9062018
11042017

[June7]

Can you always depend on year part of 4 digits and day part of two digits? If not, no manipulation can reliably convert to valid date.

Let x represent your field.

DateSerial(Right(x,4), Left(x,Len(x)-6), Mid(Right(x,6),1,2))

[Beetle]

I would probably format each value to eight characters, so you always have a 2 digit month, 2 digit day and 4 digit year, then use a combination of Left, Mid and Right functions to break it up and add in the slash charaters, then typcast the result back to a date;

CDate(Left(Format([YourField],"00000000"),2) & "/" & Mid(Format([YourField],"00000000"),3,2) & "/" & Right(Format([YourField],"00000000"),4))

Others may have better suggestions.

[tiger6115]

June7 From the 1500+ records on this one worksheet, i can see that the year is consistently 4 digits yyyy and the days are consistently 2 digits. I'm assuming that instead of Access' automated import process, i would need to create VBA code to initiate the import process and i would include the code you provided.

[June7]

Quite possibly the case. Or try the manipulation in the Excel.

=DATEVALUE(LEFT(A1,LEN(A1)-6) & "/" & MID(RIGHT(A1,6),1,2) & "/" & RIGHT(A1,4))

Then format the column.

Or Don't use DATEVALUE and just build a string.

[tiger6115]

Thanks June7, your formula worked to format the dates perfectly and works as a quick and dirty workaround. I'll work on automating it by making some Macros in Excel. I'll cleanup the data before importing into Access.

Beetle, thanks for your suggestion. When i have more time, I'll give it a try in Access.

[Beetle]

Actually, the first suggestion by June7 using DateSerial is a bit cleaner and simpler to write, so on the Access side I would probably use that.