+ Reply to Thread
Results 1 to 133 of 133

text to dates?

  1. #1
    Forum Contributor
    Join Date
    03-11-2005
    Posts
    115

    text to dates?

    Hi everyone, I have a problem with this following query:

    I have a list of cells in the text format: 01.01.05 which relates to 1st Jan 2005 is there a way to change this so that it becomes a date..?

    where:
    01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).

    Thank you for all your help.
    love Amy xx

  2. #2
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  3. #3
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  4. #4
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  5. #5
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  6. #6
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  7. #7
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  8. #8
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  9. #9
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  10. #10
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  11. #11
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  12. #12
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  13. #13
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  14. #14
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  15. #15
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  16. #16
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  17. #17
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  18. #18
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  19. #19
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  20. #20
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  21. #21
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  22. #22
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  23. #23
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  24. #24
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  25. #25
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  26. #26
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  27. #27
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  28. #28
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  29. #29
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  30. #30
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  31. #31
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  32. #32
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  33. #33
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  34. #34
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  35. #35
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  36. #36
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  37. #37
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  38. #38
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  39. #39
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  40. #40
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  41. #41
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  42. #42
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  43. #43
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  44. #44
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  45. #45
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  46. #46
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  47. #47
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  48. #48
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  49. #49
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  50. #50
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  51. #51
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  52. #52
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  53. #53
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  54. #54
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  55. #55
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  56. #56
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  57. #57
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  58. #58
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  59. #59
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  60. #60
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  61. #61
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  62. #62
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  63. #63
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  64. #64
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  65. #65
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  66. #66
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  67. #67
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  68. #68
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  69. #69
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  70. #70
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  71. #71
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  72. #72
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  73. #73
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  74. #74
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  75. #75
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  76. #76
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  77. #77
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  78. #78
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  79. #79
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  80. #80
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  81. #81
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  82. #82
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  83. #83
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  84. #84
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  85. #85
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  86. #86
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  87. #87
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  88. #88
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  89. #89
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  90. #90
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  91. #91
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  92. #92
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  93. #93
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  94. #94
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  95. #95
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  96. #96
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  97. #97
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  98. #98
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  99. #99
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  100. #100
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  101. #101
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  102. #102
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  103. #103
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  104. #104
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  105. #105
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  106. #106
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  107. #107
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  108. #108
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  109. #109
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  110. #110
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  111. #111
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  112. #112
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  113. #113
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  114. #114
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  115. #115
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  116. #116
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  117. #117
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  118. #118
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  119. #119
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  120. #120
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  121. #121
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  122. #122
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  123. #123
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  124. #124
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  125. #125
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  126. #126
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  127. #127
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  128. #128
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




  129. #129
    Mangesh Yadav
    Guest

    Re: text to dates?

    Suppose your text string is in A1, then use:

    =DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


    Mangesh




    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:

    http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  130. #130
    Ron Rosenfeld
    Guest

    Re: text to dates?

    On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
    <[email protected]> wrote:

    >
    >Hi everyone, I have a problem with this following query:
    >
    >I have a list of cells in the text format: 01.01.05 which relates to
    >1st Jan 2005 is there a way to change this so that it becomes a
    >date..?
    >
    >where:
    >01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    >Thank you for all your help.
    >love Amy xx


    If your dates are in columns:

    Select the dates.

    Data/Text-to-Columns Fixed Width (i.e. don't separate them)
    Next
    Next
    Column Data Format/Date: DMY
    Finish


    --ron

  131. #131
    KL
    Guest

    Re: text to dates?

    how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

    Regards,
    KL


    "AmyTaylor" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi everyone, I have a problem with this following query:
    >
    > I have a list of cells in the text format: 01.01.05 which relates to
    > 1st Jan 2005 is there a way to change this so that it becomes a
    > date..?
    >
    > where:
    > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >
    > Thank you for all your help.
    > love Amy xx
    >
    >
    > --
    > AmyTaylor
    > ------------------------------------------------------------------------
    > AmyTaylor's Profile:
    > http://www.excelforum.com/member.php...o&userid=20970
    > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    >




  132. #132
    Dave Peterson
    Guest

    Re: text to dates?

    With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

    But I think (untested) if the short date format matched (dmy), then the formula
    would work fine.



    KL wrote:
    >
    > how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >
    > Regards,
    > KL
    >
    > "AmyTaylor" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Hi everyone, I have a problem with this following query:
    > >
    > > I have a list of cells in the text format: 01.01.05 which relates to
    > > 1st Jan 2005 is there a way to change this so that it becomes a
    > > date..?
    > >
    > > where:
    > > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    > > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    > >
    > > Thank you for all your help.
    > > love Amy xx
    > >
    > >
    > > --
    > > AmyTaylor
    > > ------------------------------------------------------------------------
    > > AmyTaylor's Profile:
    > > http://www.excelforum.com/member.php...o&userid=20970
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401411
    > >


    --

    Dave Peterson

  133. #133
    KL
    Guest

    Re: text to dates?

    Yup, that was exactly the idea as the OP appears to be using the European
    notation.

    Regards,
    KL


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > With my windows short date set for mm/dd/yyyy, I got May 10, 2005.
    >
    > But I think (untested) if the short date format matched (dmy), then the
    > formula
    > would work fine.
    >
    >
    >
    > KL wrote:
    >>
    >> how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.
    >>
    >> Regards,
    >> KL
    >>
    >> "AmyTaylor" <[email protected]>
    >> wrote
    >> in message news:[email protected]...
    >> >
    >> > Hi everyone, I have a problem with this following query:
    >> >
    >> > I have a list of cells in the text format: 01.01.05 which relates to
    >> > 1st Jan 2005 is there a way to change this so that it becomes a
    >> > date..?
    >> >
    >> > where:
    >> > 01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
    >> > 05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).
    >> >
    >> > Thank you for all your help.
    >> > love Amy xx
    >> >
    >> >
    >> > --
    >> > AmyTaylor
    >> > ------------------------------------------------------------------------
    >> > AmyTaylor's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=20970
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=401411
    >> >

    >
    > --
    >
    > Dave Peterson




+ 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