Question

Locked

Convert Excel Date String to Date type

By janania ·
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.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Well that being a completely non standard date format

by Tony Hopkinson In reply to Convert Excel Date String ...

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.

Collapse -

Thanks.

by janania In reply to Well that being a complet ...

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

Collapse -

Do it in bits

by Tony Hopkinson In reply to Thanks.

"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.

Collapse -

Awesome!

by janania In reply to Do it in bits

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

Collapse -

my email id is sanjaykumar84@hotmail.com

by sanjaykumar84 In reply to Do it in bits

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.

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums