convert text to date

cloud168

New Member
Joined
Oct 12, 2009
Messages
19
Hi im trying to convert column A dates into date values as my dates contain text which cannot be converted by =datevalue() in excel

Im trying to write the following code but it doesnt work. Can anyone pls help?

Option Explicit

Sub converTextToDate()
Dim Current_Date As Date
Dim Date_String As Object
Dim myrange As Object
Dim DATERANGE As Object

Set myrange = ActiveSheet.Range("A6:A150")
Range("A6:A13").Select
Set DATERANGE = ActiveSheet.Range("A6:A150")
Date_String = Range("A6:A13").Value
Current_Date = CDate(Date_String)
Range("H6:H13").Select
Range("H6:H13").Value = Current_Date
End Sub
 

cloud168

New Member
Joined
Oct 12, 2009
Messages
19
there may be some invisible character in your copied dates...

try the following to see...
=DATEVALUE(CLEAN(A1))
or
=DATEVALUE(SUBSTITUTE(A1,CHAR(160),""))

assume your date system is set at MDY

Good luck.

Hi thanks for trying to help. Unfortunately it doesnt work. It returns #VALUE
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
can you perform this test:


Input this formula in a blank cell (where A1 is any of your dates)
=CODE(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1))

then press F9 in edit mod.

Pls copy what do you get in the formula bar.
 

cloud168

New Member
Joined
Oct 12, 2009
Messages
19
can you perform this test:


Input this formula in a blank cell (where A1 is any of your dates)
=CODE(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1))

then press F9 in edit mod.

Pls copy what do you get in the formula bar.

End result is 50 from the formula below

=CODE(MID($A$15,ROW(INDIRECT("1:"&LEN($A$15))),1))
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
in the cell with formula, press F2 and then F9
you should see something like:
={49;47;49;47;50;48;49;53;160}

Pls tell me what you've got; and also what the date is referring to.


End result is 50 from the formula below

=CODE(MID($A$15,ROW(INDIRECT("1:"&LEN($A$15))),1))
 

cloud168

New Member
Joined
Oct 12, 2009
Messages
19

ADVERTISEMENT

in the cell with formula, press F2 and then F9
you should see something like:
={49;47;49;47;50;48;49;53;160}

Pls tell me what you've got; and also what the date is referring to.

={50;49;47;48;49;47;50;48;49;53}
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
The date it refers to is
21/01/2015
right?

I guess the date setting in your computer is M/D/Y; but your data are in "D/M/Y", that's why you get #value!

Pls repeat the "Text to Columns" steps you performed but using "DMY" in step 3.

Hope you have the final luck.

={50;49;47;48;49;47;50;48;49;53}
 
Last edited:

cloud168

New Member
Joined
Oct 12, 2009
Messages
19
The date it refers to is
21/01/2015
right?

I guess the date setting in your computer is M/D/Y; but your data are in "D/M/Y", that's why you get #value!

Pls repeat the "Text to Columns" steps your performed but using "D/M/Y" in step 3.

Hope you have the final luck.

Thanks man sir. I got it :). Really thks for ur help
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,922
Latest member
defectexpress

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top