convert text to date

[cloud168]

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

 

[mfexcel]

what is your text? could you give some examples?

 

[cloud168]

what is your text? could you give some examples?

My text is just simply the date 12/1/2015 but excel couldnt recongise the date value. So im trying to convert it into a date value which excel can recognise.

 

[mfexcel]

Have you tried "Text to Column"?

My text is just simply the date 12/1/2015 but excel couldnt recongise the date value. So im trying to convert it into a date value which excel can recognise.

 

[cloud168]

Have you tried "Text to Column"?

It doesnt work.

This code work but its only for a particular cell.
How do i make it to be a range of cells


Sub converTextToDate()

Dim Current_Date As Date
Dim Date_String As String

Range("A1").Select
Date_String = Range("A1").Value
Current_Date = CDate(date_String)
Range("C1").Select
Range("C1").Value = Current_Date

End Sub
</PRE>

 

[cloud168]

It doesnt work.

This code work but its only for a particular cell.
How do i make it to be a range of cells


Sub converTextToDate()

Dim Current_Date As Date
Dim Date_String As String

Range("A1").Select
Date_String = Range("A1").Value
Current_Date = CDate(date_String)
Range("C1").Select
Range("C1").Value = Current_Date

End Sub

</PRE>

Hi anyone kind enough to help me please

 

[mfexcel]

I am curious why the "Text to Column" failed. Would you mind describing the steps you have performed?

Sorry that I don't know vba much...

Hi anyone kind enough to help me please

 

[Blade Hunter]

Check the regional settings on your machine are correct for your environment, I have run into issues with this before where it would convert dates with a date of 1 to 12 but not higher as it thought it was a month when it was actually the day

 

[cloud168]

Check the regional settings on your machine are correct for your environment, I have run into issues with this before where it would convert dates with a date of 1 to 12 but not higher as it thought it was a month when it was actually the day

Hi mfexcel, the data are actually copied over from another system so the dates when copied into excel are in text format and when i try to use excel function =datevalue() it cant be converted nor can it be converted by text to column.

The steps are as follow

1) click text to column
2) next
3) next
4) click on tab date and choose MDY
5) finish

After i finish the steps it still doesnt have a value

 

[mfexcel]

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 mfexcel, the data are actually copied over from another system so the dates when copied into excel are in text format and when i try to use excel function =datevalue() it cant be converted nor can it be converted by text to column.

The steps are as follow

1) click text to column
2) next
3) next
4) click on tab date and choose MDY
5) finish

After i finish the steps it still doesnt have a value