How can I convert Text into Date? (See inside)

[Yonah Sudwerts]

I have cells that contain Dates such as "Aug 23 2009" but, they were copy
pasted in, and Excell does not see them as dates, is there anything I can
run that can convert them?

I want to be able in the end, that when I open Excell, it can jump to
Today;s date. And alternatively, to have some kind of search option, that
will lead me to any date I want.

But that is part 2, just looking to get the text recognized as Dates to
start.

Thanks in Advance,
Yoni

 

[Jake Marx]

Hi Yoni,

Something like this may work for you:

Public Sub ConvertDatesInActiveSheet()
Dim c As Range

For Each c In ActiveSheet.UsedRange
If IsDate(c.Value) And Not IsNumeric(c.Value) Then
'/ convert it
c.Value = CDate(c.Value)
End If
Next c
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Ajtb]

Hi Yonah

[A1] = Aug 23 2009
[A2] = next date

and so on

Select all dates in column 1 and then through the data menu, do Text To
Columns ... space delimited.

This will put the month in column 1, the day in column 2 and the year in
column 3.

Now in column 4, start with cell [D1], type in the following:

=TRIM(B1) & TRIM(A1) & TRIM(C1)

It will put 23Aug2009 in [D1].
Auto fill down.

Copy and paste values only to remove formulae once you have finished.

You now have dates that Excel will recognize.

HTH
Andrew Bourke

 

[Yonah Sudwerts]

Thanks, that worked, only think is, I got about 30-something sheets in the
Book, How can I run it on the entire book?

Hi Yoni,

Something like this may work for you:

Public Sub ConvertDatesInActiveSheet()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If IsDate(c.Value) And Not IsNumeric(c.Value) Then
'/ convert it
c.Value = CDate(c.Value)
End If
Next c
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have cells that contain Dates such as "Aug 23 2009" but, they were
copy pasted in, and Excell does not see them as dates, is there
anything I can run that can convert them?

I want to be able in the end, that when I open Excell, it can jump to
Today;s date. And alternatively, to have some kind of search option,
that will lead me to any date I want.

But that is part 2, just looking to get the text recognized as Dates
to start.

Thanks in Advance,
Yoni

 

[Yonah Sudwerts]

Never Mind, I just did it sheet by sheet.

Hi Yonah

[A1] = Aug 23 2009
[A2] = next date

and so on

Select all dates in column 1 and then through the data menu, do Text To
Columns ... space delimited.

This will put the month in column 1, the day in column 2 and the year in
column 3.

Now in column 4, start with cell [D1], type in the following:

=TRIM(B1) & TRIM(A1) & TRIM(C1)

It will put 23Aug2009 in [D1].
Auto fill down.

Copy and paste values only to remove formulae once you have finished.

You now have dates that Excel will recognize.

HTH
Andrew Bourke

I have cells that contain Dates such as "Aug 23 2009" but, they were copy
pasted in, and Excell does not see them as dates, is there anything I can
run that can convert them?

I want to be able in the end, that when I open Excell, it can jump to
Today;s date. And alternatively, to have some kind of search option, that
will lead me to any date I want.

But that is part 2, just looking to get the text recognized as Dates to
start.

Thanks in Advance,
Yoni

 

[Jake Marx]

Hi Yoni,

Thanks, that worked, only think is, I got about 30-something sheets
in the Book, How can I run it on the entire book?

I see that you've already done it sheet by sheet, but for future reference,
this example shows you how you can loop through the worksheets in a
workbook:

Public Sub ConvertDatesInAllSheets()
Dim ws As Worksheet
Dim c As Range

For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
If IsDate(c.Value) And _
Not IsNumeric(c.Value) Then
'/ convert it
c.Value = CDate(c.Value)
End If
Next c
Next ws
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

Jake, I used your VB code below, just what I need, but when I run it on some
sheets I get a

Run-time error 1004:
Application-defined or object-defined error

When you press debug, it highlights in yellow c.Value = CDate(c.Value)

You will probally guess I'm now VB code expert!

Any idea why it would only do this in some sheets?

Hi Yoni,

Something like this may work for you:

Public Sub ConvertDatesInActiveSheet()
Dim c As Range

For Each c In ActiveSheet.UsedRange
If IsDate(c.Value) And Not IsNumeric(c.Value) Then
'/ convert it
c.Value = CDate(c.Value)
End If
Next c
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have cells that contain Dates such as "Aug 23 2009" but, they were
copy pasted in, and Excell does not see them as dates, is there
anything I can run that can convert them?

I want to be able in the end, that when I open Excell, it can jump to
Today;s date. And alternatively, to have some kind of search option,
that will lead me to any date I want.

But that is part 2, just looking to get the text recognized as Dates
to start.

Thanks in Advance,
Yoni