When using
Tool #1375 Data Transfers (Upload or Download) to download a system-generated file and save to an Excel file, sometimes the date fields save as a number (such as 61219) rather than a date (6/12/2019 or 6/12/19). This number can't be converted to a date in Excel except by manually retyping each date. The tool has a format button, but this appears to have no effect on the file that is produced.
The tool itself is an IBM tool so we cannot alter how it works, but we do have a few techniques that have worked in these situations.
- If you have tracked along with some of the “database views” (such as MBRNACCTSQ) you’ll notice these views contain full date values and will download as date values. Additionally, we implemented the DATELOOKUP file which can be used to “swap out” undesired date formats in your custom reports. Building custom queries using views and that DATELOOKUP file will solve many of these tricky date formats. (Note: To see a list of available database views, launch Tool #332 Database Search Assistant and look in the far-right column.)
- If you are not using Query, there are a few tricks on the Excel side as well. The Excel “wizard” can be launched on demand in Excel by highlighting the entire date column you wish to format, selecting the DATA tab in the Excel ribbon bar, and clicking the “Text to Column” button. This workflow will launch the text to column conversion wizard, allowing you to modify date layouts for that field.
- It is also possible to write custom Excel formulas which can convert those numeric dates into true date value.
The method you use depends on how the file was created. But in all cases, the Excel “Text to Columns” option is universally helpful to make those changes on a per-column basis once you do have the data into Excel:
System-generated files/Tool exports = Excel “Text to Columns” wizard
Query (using numeric date values) = DATELOOKUP table first. If not, then Excel “Text to Columns” wizard
Query (using alphanumeric/text values) = Excel “Text to Columns” wizard
or combo of Query custom fields and DATELOOKUP table