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

  • Thread starter Thread starter Yonah Sudwerts
  • Start date Start date
Y

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
 
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]
 
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
 
Thanks, that worked, only think is, I got about 30-something sheets in the
Book, How can I run it on the entire book?

Jake Marx said:
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]


Yonah said:
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
Click to expand...
Click to expand...
 
Never Mind, I just did it sheet by sheet.
Ajtb said:
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 said:
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
Click to expand...
Click to expand...
 
Hi Yoni,

Yonah said:
Thanks, that worked, only think is, I got about 30-something sheets
in the Book, How can I run it on the entire book?
Click to expand...

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]
 
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?

Jake Marx said:
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]


Yonah said:
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
Click to expand...
Click to expand...
 
Back
Top