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

• How to Use Pivot Table in Excel

  Release Date: 07/18/2024 Version: 8.6 & Up In this video, we demonstrate a powerful feature in Excel called the "Pivot Table." While this subject is not directly related to Elliott Business Software, we do have a Report Desk feature that can export ...

• How to Make Excel Show Negative as Red by Default When Bringing up a CSV File

  Release Date: 10/16/2023 Version: 7.x & Up Q - During the Elliott 2023 conference, at the Financial Statement with Excel session, I saw you were able to bring up the CSV file in Excel that shows the negative amount with red color. I know how to ...

• How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019

  Release Date: 06/30/2020 Version: 7.x & Up Elliott's data is based on the PSQL database engine. In Excel, you can access the PSQL database through an ODBC interface with the Microsoft "Query" function. The following procedure outlines how you can do ...

• Microsoft Query Is Missing on Excel After Office 365 Update

  Release Date: 06/07/2024 Version: N/A Q: We use Microsoft Query extensively to retrieve data from Elliott database. After the last update for Office 365, my Microsoft Query is missing on Excel. How can I recover this feature? A: The solution is to go ...