+ Reply to Thread
Results 1 to 6 of 6

Converting yyyymm to date

  1. 03-27-2013, 05:13 PM #1
    Platinum3x is offline
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Converting yyyymm to date

    Hi,

    I have several thousand lines of date data in a column and it comes out in the following format:

    Please Login or Register  to view this content.
    etc... where, 2012 is the year and 02,03,04 are the months (yyyymm). For the life of me, I can't figure out how to format this as Feb-12, Mar-12, Apr-12, May-12 Etc....

    Any help is much appreciated!
    Reply With QuoteRegister To Reply
  2. 03-27-2013, 05:21 PM #2
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,667

    Re: Converting yyyymm to date

    formula:
    Please Login or Register  to view this content.
    Celformat:
    mmm-yyyy
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  3. 03-27-2013, 05:22 PM #3
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Converting yyyymm to date

    =right(a1,2)&"-"left(a1,4)

    in which a1 = 201202

    format the cell as mm-yyyy
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 03-27-2013, 05:23 PM #4
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Converting yyyymm to date

    Try

    =(TEXT(A1,"0000-00")&"-01")+0

    Then format the cell with the formula as a date.
    Reply With QuoteRegister To Reply
  5. 03-27-2013, 05:24 PM #5
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Converting yyyymm to date

    Note, popipipo is from Holland, being in the USA you will need to change their ; to , (due to regional formatting)

    @ oeldere, you left out an &...and the answer is text, not date...
    =DATE(LEFT(B7,4),RIGHT(B7,2),1)
    Last edited by FDibbins; 03-27-2013 at 05:30 PM.
    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
    Reply With QuoteRegister To Reply
  6. 04-06-2013, 11:08 AM #6
    Platinum3x is offline
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Converting yyyymm to date

    Thanks for the help guys! I ended up going with the following:

    Please Login or Register  to view this content.
    Set custom formatting to mmm-yy

    With date in E2 formatted as 201202, this returns Feb-2012. Exactly what I needed!

    ~Plat
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1