+ Reply to Thread
Results 1 to 5 of 5

Help: How do I convert a text date into a real date format

  1. #1
    Registered User
    Join Date
    02-26-2004
    Location
    philippines
    Posts
    73

    Help: How do I convert a text date into a real date format

    Hi Guys,

    Does anyone know how to convert a date that's inputted in a text format?

    Example: The date is entered as 62606, this means as 6/26/06. I just can't change the format into date because it will be different.

    I'm thinking if there is some sort of formula to add a hyphen and turn it to 6-26-06. Like extracting, the last two digit, then the two middle, then the first

    Hope someone can help me. Thanks in advance.

  2. #2
    Guest

    Re: How do I convert a text date into a real date format

    Hi

    What if the 'date' is 10606? Is that 1/06/06 or is it 10/6/06?

    Andy.

    "japorms" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi Guys,
    >
    > Does anyone know how to convert a date that's inputted in a text
    > format?
    >
    > Example: The date is entered as 62606, this means as 6/26/06. I just
    > can't change the format into date because it will be different.
    >
    > I'm thinking if there is some sort of formula to add a hyphen and turn
    > it to 6-26-06. Like extracting, the last two digit, then the two
    > middle, then the first
    >
    > Hope someone can help me. Thanks in advance.
    >
    >
    > --
    > japorms
    > ------------------------------------------------------------------------
    > japorms's Profile:
    > http://www.excelforum.com/member.php...fo&userid=6544
    > View this thread: http://www.excelforum.com/showthread...hreadid=567496
    >




  3. #3
    CLR
    Guest

    RE: Help: How do I convert a text date into a real date format

    Maybe this............

    =IF(RIGHT(A1,2)*1<=6,DATE(RIGHT(A1,2)*1+2000,LEFT(A1,LEN(A1)-4)*1,MID(A1,LEN(A1)-3,2)*1),DATE(RIGHT(A1,2)*1+1900,LEFT(A1,LEN(A1)-4)*1,MID(A1,LEN(A1)-3,2)*1))

    All on one line, watch out for wordwrap......

    Vaya con Dios,
    Chuck, CABGx3



    "japorms" wrote:

    >
    > Hi Guys,
    >
    > Does anyone know how to convert a date that's inputted in a text
    > format?
    >
    > Example: The date is entered as 62606, this means as 6/26/06. I just
    > can't change the format into date because it will be different.
    >
    > I'm thinking if there is some sort of formula to add a hyphen and turn
    > it to 6-26-06. Like extracting, the last two digit, then the two
    > middle, then the first
    >
    > Hope someone can help me. Thanks in advance.
    >
    >
    > --
    > japorms
    > ------------------------------------------------------------------------
    > japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
    > View this thread: http://www.excelforum.com/showthread...hreadid=567496
    >
    >


  4. #4

    Re: Help: How do I convert a text date into a real date format

    Well do you need the date to be read as a date by Excel? or only by the
    user?

    If you don't need Excel to read the number as a date, then there is a
    very simple way to solve your problem.

    Right click in the cell where the date is to go.
    Choose Format Cells from the dropdown.
    Click the Number Tab
    Category: Custom

    In the Type: Box where it says "General" type
    00-00-00
    or
    00/00/00
    depending on whether you'd prefer hyphens or slashes.

    And then Click OK.

    You can now type in 062606 or 62606 and it will be changed to 06/26/06
    or 06-26-06.
    Excel won't read it as date, but it will look like a date.

    It depends on what else you need the cell to do (i.e. do any other
    cells refer to this cell in a formula?) If that is the case then this
    won't work for you, but if you only need it to "look" like a date for
    printed forms or whatever, this should work.



    japorms wrote:
    > Hi Guys,
    >
    > Does anyone know how to convert a date that's inputted in a text
    > format?
    >
    > Example: The date is entered as 62606, this means as 6/26/06. I just
    > can't change the format into date because it will be different.
    >
    > I'm thinking if there is some sort of formula to add a hyphen and turn
    > it to 6-26-06. Like extracting, the last two digit, then the two
    > middle, then the first
    >
    > Hope someone can help me. Thanks in advance.
    >
    >
    > --
    > japorms
    > ------------------------------------------------------------------------
    > japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
    > View this thread: http://www.excelforum.com/showthread...hreadid=567496



  5. #5
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    You could use this if your day value is always 2 digits (1st = 01, 2nd=02 etc..) So October 10 is 101006 and January 1 is 10106

    =TEXT(A1,"00-00-00")*1

    Format cells as mm/dd/yy.



    HTH

    Steve

+ 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