Skip to content

Converting Elliott Internal Date to Conventional Date Format in Excel

← Systems Manager
Q - If I retrieve Elliott's data in Excel through ODBC, the date column is retrieved in an 8-digit numeric format like:20161231, where the first 4 digits are the years, the following 2 digits are the month, and the last two digits are the day of the month.  I would like the above YYYYMMDD value to be presented in an MM/DD/YYYY format (American Date format).  In this example, I would like to see the date as 12/31/2016.  How do I do that?

A - Let's say the YYYYMMDD numeric date value is stored in column B, then you can create a formula field in column C.  If this is row 2, then the formula will look like:,
   =MID(B2,5,2)&"/"&RIGHT(B2,2)&"/"&LEFT(B2,4)
MID(), RIGHT(), LEFT() are string functions supported in Excel.  The "&" character performs as the concatenate.  You can copy the value in the C2 cell and paste the rest in the column C cell to complete this.  If you use this operation all the time, then you might consider creating a macro to re-use in the future.  See sample screen below:



RSS

Feedback and Knowledge Base

(thinking…)