Thread: 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)

Thanks for your excellent help on this issue.

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

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