Viewing 19 posts - 1 through 19 (of 19 total)
  • Quick MS Excel question
  • tyger
    Free Member

    I have numbers in a cell that I want to convert to a date format.

    Example 02092011 and I want to covert it to 02/09/2011

    Help please – many thanks as always

    Rubber_Buccaneer
    Full Member

    For the example you give

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

    Where A1 is the cell you have the number in.

    If you don’t have leading zeros use

    =DATE(RIGHT(TEXT(A1,”00000000″),4),MID(TEXT(A1,”00000000″),3,2),LEFT(TEXT(A1,”00000000″),2))

    it will work but there is probably a more elegant solution.

    rondo101
    Free Member

    Use “text to columns” & “fixed width” to split the data to 3 columns of “02” “09” and “2011”.

    Then in Column D1 enter =a1&”/”&b1&”/”&c1. Drag formula down, copy column & paste special; values. Format column to date format.

    Edit – ignore me. Post above is much better.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    tyger
    Free Member

    Doesn’t seem to work for some reason

    Rubber_Buccaneer
    Full Member

    What result do you get?

    Cougar
    Full Member

    =DATEVALUE(cells) ?

    tthew
    Full Member

    Won’t changing the cell format to date work?

    Edit, er no. Nearly but returns the year 7694 instead of 2011.

    Cougar
    Full Member

    That’s why you need DATEVALUE. DATEVALUE converts it to a string that Excel understands as a date, then you Copy and Paste Special / Values the resultant cells.

    I think. Something like that anyway.

    Rubber_Buccaneer
    Full Member

    Cougar, I don’t think DATEVALUE will recognise the OP’s number as date text.

    tron
    Free Member

    Mash the text into a date format by whatever method, then do copy, paste values or paste values and format.

    Lots of functions will not work with the output of other functions..

    tron
    Free Member

    Oh, and Leading Zeroes don’t exist in excel (if you can see them, that’s because someone’s set the number format as 00000000 – press Ctrl 1 to see it)…

    You’ll need to work from the right to get it into the right format. Take the first 4 digits on the right to get the year, then the next two to establish the month, then whatever’s left to get the first digit. Not got the time to work out the formulae, but I’d do it with a load of columns (ie, one for year, one for month etc.), using LEFT, RIGHT, MID and LEN functions.

    Left takes a chunk off the left, Right- I think you can guess, and Mid takes a chunk out the middle.

    Len gives you the length of whatever’s in the cell so you can still make things work if the length of your string varies.

    An alternate method would be to concatenate a 9 (all months should be at most 31 days long) onto the front of every leading zero date, and then strip that out later. This would mean you could just take the left two digits, the middle 2 digits, and the last 4 digits using pretty simple formulae.

    You’d get your 9 with something like =if(“(Len(A2)<8”),(concatenate(“9”,A2),) if your first date is in A2. Copy and paste values again to strip out the formulae…

    You’d then do another if statement to pull out the right hand digit if the number starts with a 9…

    Cougar
    Full Member

    Cougar, I don’t think DATEVALUE will recognise the OP’s number as date text.

    Aha. You may have a point there. (-:

    In that case, I’m oot.

    tron
    Free Member

    Worked it out. Drop me an email.

    Cougar
    Full Member

    Is it a secret?

    tron
    Free Member

    Nope, it’s just a load of columns with LEFT, RIGHT and LEN functions and it’s a bit hard to follow from cut and paste…

    Set out the below across Row 1 – the | sign is there to show the breaks between columns:
    Original | Year | Month |Length | Day | Date | Paste values

    Your date goes into cell A2…

    Cell B2:
    =RIGHT(A2,4)

    C2:
    =LEFT((RIGHT(A2,6)),2)

    D2:
    =LEN(A2)

    E2:
    =LEFT(A2,(D2-6))

    F2:

    =CONCATENATE(E2,”/”,C2,”/”,B2)

    You then copy the contents of column F and Paste Values into Column G.

    Set the format as Date or Datestring it, and you’re sorted.

    All the above could be condensed into one formula, but it’d make your brain ache…

    Stoner
    Free Member

    =DATEVALUE(((LEFT(A1, 2)&”/”&RIGHT(LEFT(A1, 4), 2)&”/”&RIGHT(A1, 4))))

    pennine
    Free Member

    As usual, be patient and Stoner will pop up with one of his rather nice solutions 😀

    swedishmatt
    Free Member

    Stoner shouldn’t that be a 2 instead of the first 4?

    Stoner
    Free Member

    matt – no. Im carving 2 chars from the right side of a 4 char carve to the left. i.e. the 3rd and 4th chars from a n length chain.

    ps , dont forget to format the formula cell in date format or you just get the numerical date value.

    pps: pennine, too kind 🙂

Viewing 19 posts - 1 through 19 (of 19 total)

The topic ‘Quick MS Excel question’ is closed to new replies.