Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Converting Dates

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a few hundred dates
    reading 1020312 etc
    which I want to convert to a date either
    120302 or tuesday 12 march 2002
    can this be done?

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One way:

    With the data in A1,

    =DATE(LEFT(A1,3)+1900,MID(A1,4,2),RIGHT(A1,2))

    and format the cell as the date format you want.

    Jay

    EDIT: If you are working with dates prior to 2000 and the first 2 digits (rather than 3) are 99 for example, use the more robust, but longer formula:

    =DATE(LEFT(A1,LEN(A1)-4)+1900,MID(A1,LEN(A1)-3,2),RIGHT(A1,2))



    [ This Message was edited by: Jay Petrulis on 2002-04-15 14:30 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Manchester UK
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this it'll change those numbers to dates wether it relevent dates i dunno gut you could merge the last formulea with this

    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 4/15/2002 by Jamie Wood
    '

    '

    Range("B18:B22").Select
    Selection.NumberFormat = "yy/dd/mm"
    End Sub

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay nothing seems to show up in the cell when you put the formula in there but when you hover over the top of the cell it tells you the correct date??

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry no it does work for some reason It brought up the answer in white text Duh

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Alot Jay thats great.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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