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)
Bookmarks