Zoomdata Version

Address SQL datasources apparently displaying date and time fields incorrectly when exported into Excel

Symptom

This behavior is not a bug or an issue with Zoomdata but actually a result of how Microsoft Excel tries to read the datetime data stored in the CSV file generated. CSV files don't have a specific format, they just contain data. When Excel tries to examine this data, it will try to set an appropriate format for it. If the user opens the CSV file using a text editor (e.g. Sublime Text), they should notice that the datetime fields and corresponding values display correctly as a string value within quotation marks. Excel is able to recognize a string containing a human-readable datetime value, but it will convert this value to its own internal timestamp value represented as a floating point number. Excel will then apply an appropriate (or default) datetime style to these cells, but the format displayed may not necessarily use or match the string representation shown in the CSV file.

When the user initially opens the CSV file in Excel and clicks on the actual datetime field, they might notice that the correct value and actual value of the cell is displayed in the "function" section even if the value shown in the specific cell is different:


Figure 1: Note that selecting the Date cells will show the actual correct datetime value

Opening the CSV file with Excel and saving might cause the file to overwrite the datetime data with the incorrect format instead. Please check the initial CSV file generated by Zoomdata during export.

To make sure Excel displays the datetime field/columns correctly, use one of the following two methods:

  1. Prefix the formatted date strings in the CSV file by adding an "=" symbol before the datetime values in quotes for each individual row (e.g. "2013-09-17 04:00:00.0","Alabama" becomes ="2013-09-17 04:00:00.0","Alabama")
  2. Format the column in Excel so that the cells use the correct datetime format by right-clicking the column (or cells) and selecting the Format Cells option. Please refer to following screenshots:


Figure 2: Right-click the datetime column(s) and select Format Cells option​



Figure 3: Under the Date or Time categories, select the correct format type.


Figure 4: Datetime fields in Excel will now display with the updated date/time format

For more information about making formatting changes (e.g. trying to set a different default datetime format) or general formatting behavior in Excel, please refer to Microsoft Excel's documentation.