Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Format (2003)

    Lounger's,

    I have a spreadsheet sent to me where the person displayed the date as YYYYmmdd - ie 20060126. I'm trying to convert to a more recognised date format -ie dd/mm/yyyy - 26/1/2006.

    Any suggestion of a formula that I could use to convert to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Format (2003)

    See the recent thread starting at <post:=550,327>post 550,327</post:>.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Format (2003)

    If they entered a date in the cell and formatted it as "yyyymmdd" all you need to do is select the cells and format cells - number (tab) and pick an alternate date format.

    If they entered the number: 20060126, you can use a formula like:

    <pre>=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))</pre>


    to convert it (assuming the number is in A1). Then you can format as desired

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format (2003)

    =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) wil give you a text representation of the date.

    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) and proper cell formating to dd/mm/yyy will give you a true date cell

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format (2003)

    Thank you all for the quick responses.

    Your assistance is much appreciated

Posting Permissions

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