+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Yonah Sudwerts
    Guest

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

    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



  2. #2
    Jake Marx
    Guest

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

    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 Sudwerts wrote:
    > 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


  3. #3
    Ajtb
    Guest

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

    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 wrote:
    > 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
    >
    >


  4. #4
    Yonah Sudwerts
    Guest

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

    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" <msnews@longhead.com> wrote in message
    news:OkdRHKnKFHA.3960@TK2MSFTNGP09.phx.gbl...
    > 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 Sudwerts wrote:
    >> 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





  5. #5
    Yonah Sudwerts
    Guest

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

    Never Mind, I just did it sheet by sheet.
    "Ajtb" <ajtbiz1@iinet.net.au> wrote in message
    news:4238acb7$0$3681$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
    > 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 wrote:
    >> 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




  6. #6
    Jake Marx
    Guest

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

    Hi Yoni,

    Yonah Sudwerts wrote:
    > 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]


  7. #7
    Nortos
    Guest

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

    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" wrote:

    > 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 Sudwerts wrote:
    > > 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

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1