Question Locked

Convert Excel Date String to Date type

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a date string with "Monday, February 02, 2007" format in a one cell column.

This is a .csv file exported to a excel file for some reason the column was changed to a string type instead a date type.

I need help in converting this date string to a date type of any format as long as it's a date type not a string.

6 replies

by tony hopkinson

means very few (if any) programs will auto convert it which is why it's coming in as a string.

I'm not an excel aficionado, but the only way I can see forward would be to import it then add a formula to manipulate the string into a recognisable date format and then convert that to a date. '02-Feb-2007' would probably be the easiest manipulation.

Course if you have control of the export, you could change it to output the date in a recognised format, but life is not usually that nice. :D

by janania

Hi Tony, thanks for your wise reply..but how is my problem now for the manipulation. I'm not a VB guru either... Let's see what I can come up with if any...there goes my weekend :(

by tony hopkinson

"Monday, February 02, 2007" in A1

b1 = Mid(instr(",",A1)+2,20) gives "February 02, 2007"
then make the following columns strings with format

c1 = Mid(1,instr(" ",b1) -1,20 gives "February"
d1 = Mid(instr(",",b1)-2,2) gives 2
e1 = mid(instr(",",b1)+2,4) gives 2007
f1 = d1 & "-" & Left(c1,3) & "-" & e1

the format F column as date.

My vba is iffy as well, but something like this should work, no doubt an excel expert could give a better way.

by janania

Hi Tony, I used find() instead of InStr(). and Voila! it worked. Thanks a lot!

by sanjaykumar84

Hi,

I have a data which is in the format xx.xx.xxxx which i want to convert to date type. But out of 200 records only 50 are changing. rest are not changing. what could be the reason.

This discussion is closed to new replies.