Converting Elliott Internal Date to Conventional Date Format in Excel

Converting Elliott Internal Date to Conventional Date Format in Excel

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



    • Related Articles

    • Convert Elliott Internal Date to Proper Date Format in Third-Party Tools (Crystal Reports)

      Release Date: 07/08/2024 Version: 8.5 & Up ELIDATE2SQL() User-Defined Function Elliott's internal database stores a date field as an 8-digit numeric in a YYYYMMdd format where YYYY is the 4-digit year, MM is the number of the month, and dd is for the ...

    • Converting Elliott Physical Server to a Virtual Machine (VM)

      Release: 4/29/2021 Version: 7.5 & Up Q - We are going to move our Elliott physical server to a Virtual Machine (VM). We would like to do this with minimal effort. One tool that we are evaluating to do this is called Disk2Vhd. Have you ever ...

    • CSV Import Supports Both MM/DD/YY and MM/DD/YYYY Date Formats

      Release Date: 12/18/2018 Q - Recently, I tried to import a future price code file through CSV. I saw that on the screen layout, it states the date format should be in the MM/DD/YY format. MM stands for the two-digit month. DD stands for the two-digit ...

    • Avalara Setup Procedures - Elliott Configuration

      Release Date: 4/28/23 Version: 8.6 and above Revised: 9/11/23 Elliott Configuration After installing Elliott V8.6 for the first time, you will need to make some configuration changes. Bring up <ElliottRoot>\Bin86\EL860CF.exe utility or alternatively ...

    • Crystal Report Conversion for V8.5

      A major improvement in Version 8.5 is the support of alphanumeric document numbers. This significant change adds flexibility and addresses the pressing issue that many Elliott users faced, which is running out of order and invoice numbers. The ...