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
 
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:
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top