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 ...

    • Elliott V7.5 Release Notes: What's New Since Elliott V7.4

      What’s New Since Elliott V7.4 Elliott 7.5 Overview Netcellent has rewritten all the legacy manufacturing modules for Elliott V7.5 and consolidated them into the BOMP module. It simplifies manufacturing data collection while providing powerful ...

    • 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 ...

    • Elliott V8.6 Configuration Utility (EL860CF.EXE)

      Configuring Elliott V8.6 After installing Elliott V8.6 for the first time, you will need to make some configuration changes. If this is the first time you have installed Elliott V8.6, the system will detect that the EL860.CFG file does not exist in ...