+ Reply to Thread
Results 1 to 5 of 5

Excel Date Conversions from YYYYYMMDD to MMDDYYYY formats

  1. #1
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169

    Question Excel Date Conversions from YYYYYMMDD to MMDDYYYY formats

    Hi - I'm working now on a special project where DB2 tables are accessed and dates are stored in YYYYMMDD formats on the mainframe. Our software package can create CSV files that work great and in fact during conversion I can highlight specific fields to format in the MDY mode if needed. However in reporting, users may want to see YYYYMMDD as it matches the mainframe (and we can store this as a numeric rather than date field).

    Below are two challenges, I've solved and am looking for any tips from the experts in the forums:

    1. What is easiest technique for converting YYYYMMDD in a spreadsheet to MM/DD/YYYY? (from a numeric field to a true date field if needed)

    CURRENT TECHNIQUE: I can parse out date components with LEFT, MID, and RIGHT to reformat these to individual cells and concatenate the 3 cells with "/" ... It's not difficult but there might be a quicker way as this format is not a predefined natural date field.

    2. What is easiest technique for formatting YYYYMMDD in a spreadsheet to YYYY/MM/DD for presentation to users?

    CURRENT TECHNIQUE: I can parse out date components with LEFT, MID, and RIGHT to reformat these to individual cells and concatenate the 3 cells with "/" ... Maybe there's a special format command where I can setup a custom pattern and place "/" in them.



    P.S. Currently using Excel 2007 or higher (actually prefer Excel 2010 beta on most PCs and keep 2007 on one of my work PCs to ensure absolute compatibility)

  2. #2
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Excel Date Conversions from YYYYYMMDD to MMDDYYYY formats

    This should answer both your questions:

    If 20040318 is in cell A1 put this formula in another cell:

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

    Now Format that cell with a date format and the cell will display 03/18/2004.
    You can also now Custom Format and display it as "yyyy/mm/dd" or "yyyy/dd/mm"

    If you want to place your date back into cell A1,
    then copy the cell with the formula
    and then Paste Special - 'Values and Number Formats',
    back into A1.

    The new date information is now in a form recognized by Excel
    and you can do what you want with it.

  3. #3
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169

    Re: Excel Date Conversions from YYYYYMMDD to MMDDYYYY formats

    Thanks for your excellent help on this issue.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Excel Date Conversions from YYYYYMMDD to MMDDYYYY formats

    Try

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

    and format in required date format

    ......or you can avoid formulas altogether and use Text to columns functinality

    Select column of data in format YYYYMMDD

    Data > text to columns > Next > Next > under "column data format" select "date" and YMD > Finish
    Last edited by daddylonglegs; 03-19-2010 at 11:06 AM.

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Excel Date Conversions from YYYYYMMDD to MMDDYYYY formats

    Hi,

    Also take a look at Data > Text to columns where you can pick your "Date" column and in Step 3 change the format of that date to your requirements
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1