Date Time Formattin, CSV, EXCEL, ACCESS

garyrobbins
garyrobbins used Ask the Experts™
on
I have a CSV file that gives the datetime as a text cell 20150615160228000  and i need to convert it to date time in access querry.

Need to convert to 06/15/2015 16:02:28

Below is what i tried but not working.

CDate(Mid([ColumnX],5,2) & "/" & Right([ColumnX],2) & "/" & Left([ColumnX],4) & " " & Mid([ColumnX],9,2) & ":" & Mid([ColumnX],11,2) & ":" & Mid([ColumnX],13,2))

The below works when it is just the date.

Dispense Date: CDate(Mid([ColumnY],5,2) & "/" & Right([ColumnY],2) & "/" & Left([ColumnY],4))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Distinguished Expert 2017
Commented:
You are using right when you should be using mid to get the day.

CDate(Mid([ColumnX],5,2) & "/" & Mid([ColumnX],7,2) & "/" & Left([ColumnX],4) & " " & Mid([ColumnX],9,2) & ":" & Mid([ColumnX],11,2) & ":" & Mid([ColumnX],13,2))
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
This is a bit simpler:

DispenseDate: CDate(Format(Left([ColumnX], 14), "@@@@\/@@\/@@ @@\:@@\:@@"))

/gustav
garyrobbins

Author

Commented:
AHHHHHH,   I should have caught that.  Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial