+ Reply to Thread
Results 1 to 7 of 7

Turning '20121101' into recognized date format

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    Fort Lauderdale, Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    Turning '20121101' into recognized date format

    I can't get my database imports into a format Excel will automatically recognize as a date.
    They all look like this:
    20121101
    20121001
    20120901 etc...

    The day is actually immatterial; it's always the first. I only need the month and year.

    I tried recording a macro that would rewrite the date, but it just did the same thing in every cell (and didn't seem to serve the ultimate purpose of of making the cells date-recognizable under the "format cell" option any way).

    Thanks for any help you can provide!

    -Travis
    Attached Files Attached Files

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Turning '20121101' into recognized date format

    You could try this in cell D2 then fill down: =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

    - Moo

  3. #3
    Registered User
    Join Date
    01-15-2008
    Posts
    16

    Re: Turning '20121101' into recognized date format

    How about:

    =Concatenate(mid(cellreference,4,2),"/",right(cellreference,2),"/",left(cellreference,4))

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Turning '20121101' into recognized date format

    in D2, copied down...
    =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-06-2012
    Location
    Fort Lauderdale, Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    [Solved]Re: Turning '20121101' into recognized date format

    Thank you guys for your speedy help!
    Moo-your fix worked!
    Bill-I got a NAME? error, for some reason, with yours.

    Really appreciate this forum. You guys helped me a ton here...
    Best,
    -Travis

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Turning '20121101' into recognized date format

    CriticalBill,

    While your formula, with a minor modification (change the first 4 in your formula to a 5) will produce something that looks like a date, it is not actually treated as such by Excel. It is a text string. You would have to apply a mathematical operation to it to make it a number, such as *1 added to the end of your formula. Then you would have to change the format of the new cells to Date.

    By using the DATE formula I proposed, it actually converts the text to date format all at once - no need for additional steps.

    - Moo

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Turning '20121101' into recognized date format

    Glad to help, roykosghost!

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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