+ Reply to Thread
Results 1 to 10 of 10

Convert Date to STring

  1. #1
    Registered User
    Join Date
    10-05-2005
    Posts
    7

    Convert Date to STring

    How do I convert a date (25/11/2005) in a date field to the same string in a General or Text field? Whenever I try Excel converts to the serial value of the date.

  2. #2
    Anne Troy
    Guest

    Re: Convert Date to STring

    =TEXT(A1,1)

    ************
    Hope it helps!
    Anne Troy
    www.OfficeArticles.com
    Check out the NEWsgroup stats!
    Check out: www.ExcelUserConference.com

    "tonymaguire" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > How do I convert a date (25/11/2005) in a date field to the same string
    > in a General or Text field? Whenever I try Excel converts to the
    > serial value of the date.
    >
    >
    > --
    > tonymaguire
    > ------------------------------------------------------------------------
    > tonymaguire's Profile:
    > http://www.excelforum.com/member.php...o&userid=27819
    > View this thread: http://www.excelforum.com/showthread...hreadid=512562
    >




  3. #3
    flummi
    Guest

    Re: Convert Date to STring

    Either format the receiving cell as date or use this formula to move
    the date across: =TEXT(A1;"MM/DD/YYYY")


  4. #4
    Biff
    Guest

    Re: Convert Date to STring

    Hi!

    Not sure what you want to do this for, but.........

    When you enter a date in a cell and Excel recognizes it as a date, it
    automatically sets that cell format to DATE. The true underlying value of
    that cell is the date serial number. So, changing the format to either
    GENERAL or TEXT, you end up with the date serial number. (when formatted as
    TEXT, then it's a TEXT number)

    You could enter the string preceded with an apostrophe: '25/11/2005, but
    it's strictly a TEXT entry and can't be used in calculations (easily).

    Biff

    "tonymaguire" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > How do I convert a date (25/11/2005) in a date field to the same string
    > in a General or Text field? Whenever I try Excel converts to the
    > serial value of the date.
    >
    >
    > --
    > tonymaguire
    > ------------------------------------------------------------------------
    > tonymaguire's Profile:
    > http://www.excelforum.com/member.php...o&userid=27819
    > View this thread: http://www.excelforum.com/showthread...hreadid=512562
    >




  5. #5
    Registered User
    Join Date
    10-05-2005
    Posts
    7
    Anne,

    I assume that A1 refers to the Cell holding the date.
    When I enter the suggested formula I get a result of 1.


    Quote Originally Posted by Anne Troy
    =TEXT(A1,1)

    ************
    Hope it helps!
    Anne Troy
    www.OfficeArticles.com
    Check out the NEWsgroup stats!
    Check out: www.ExcelUserConference.com

    "tonymaguire" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > How do I convert a date (25/11/2005) in a date field to the same string
    > in a General or Text field? Whenever I try Excel converts to the
    > serial value of the date.
    >
    >
    > --
    > tonymaguire
    > ------------------------------------------------------------------------
    > tonymaguire's Profile:
    > http://www.excelforum.com/member.php...o&userid=27819
    > View this thread: http://www.excelforum.com/showthread...hreadid=512562
    >

  6. #6
    Registered User
    Join Date
    10-05-2005
    Posts
    7
    flummi,

    thanks for the input.

    I don't want to format the receiving cell as date because the source cell is formatted as date and that is the problem that I am trying to solve. I need to achieve the following YYYYMMDD as a text field for input into a system that does not accept date formats.

    Your suggested formula generates an error.

    Regards
    Tony


    Quote Originally Posted by flummi
    Either format the receiving cell as date or use this formula to move
    the date across: =TEXT(A1;"MM/DD/YYYY")

  7. #7
    Registered User
    Join Date
    10-05-2005
    Posts
    7
    Biff,

    Thanks for response.

    Problem is that I have a file with date in date field formatted DD/MM/YYYY.

    I need to convert this to a text format cell YYYYMMDD for input into a system that requires input in text format.


    Quote Originally Posted by Biff
    Hi!

    Not sure what you want to do this for, but.........

    When you enter a date in a cell and Excel recognizes it as a date, it
    automatically sets that cell format to DATE. The true underlying value of
    that cell is the date serial number. So, changing the format to either
    GENERAL or TEXT, you end up with the date serial number. (when formatted as
    TEXT, then it's a TEXT number)

    You could enter the string preceded with an apostrophe: '25/11/2005, but
    it's strictly a TEXT entry and can't be used in calculations (easily).

    Biff

    "tonymaguire" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > How do I convert a date (25/11/2005) in a date field to the same string
    > in a General or Text field? Whenever I try Excel converts to the
    > serial value of the date.
    >
    >
    > --
    > tonymaguire
    > ------------------------------------------------------------------------
    > tonymaguire's Profile:
    > http://www.excelforum.com/member.php...o&userid=27819
    > View this thread: http://www.excelforum.com/showthread...hreadid=512562
    >

  8. #8
    flummi
    Guest

    Re: Convert Date to STring

    Hi Tony,

    It's possibly a matter of your date separators.

    This is what I tried here (Germany) and as you see it works:

    12.05.2006 12.05.2006 =TEXT(A1;"TT.MM.JJJJ")

    Try reading the help for the "=TEXT" function to find the valid
    separators in your system.

    Hans


  9. #9
    Registered User
    Join Date
    10-05-2005
    Posts
    7

    Changing Date to Text

    Hans,

    Thanks for the clue re separators.

    I changed the format to YYYYMMDD.
    Then =TEXT(A1;"YYYYMMDD")

    That successfully gave me the string in General format, but when I tried to separate the components using LEFT and RIGHT the cell still behaved like a date field.

    This problem was solved by converting from General To Text.

    Thanks for your help.

    Tony Maguire
    Melbourne Australia

  10. #10
    Ron Rosenfeld
    Guest

    Re: Convert Date to STring

    On Wed, 15 Feb 2006 00:40:36 -0600, tonymaguire
    <[email protected]> wrote:

    >
    >How do I convert a date (25/11/2005) in a date field to the same string
    >in a General or Text field? Whenever I try Excel converts to the
    >serial value of the date.


    You use the TEXT worksheet function.

    eg: =TEXT(A1,"YYYYMMDD")


    --ron

+ 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