How to Get the Date & Time to Appear Correctly in Excel Exports

2017-04-28 19:15

There will be 3 columns available for every date field. 

  • “xxxxx Date”  will show only the date in mm/dd/yy format.
  • “xxxxx DateTime” will show the date and time in “mm/dd/yy hh:mm” format.
  • “xxxxx Time” will show only the time in “hh:mm:ss” format.
    • Here is an example screen shot:

 

When exporting a grid to Excel, the DateTime column requires additional formatting in Excel to display  correctly. This appears to be an Excel issue that Envio cannot work around.

  • to export the DateTime column, an Excel macro can be created that does the formatting, and can then be reused when an export is done. Here’s what needs to be done in Excel to format the exported DateTime columns.
  • Screen shot of Envio master list with a DateTime column:

  • After exporting to a .xls file, it comes into Excel like this.  

 

  • Note that all the columns are too narrow, so first we widen the columns. Observe that the DateTime column is showing only the time, and appears to have lost the Date.

  • If we click into one of the DateTime cells, Excel recognizes it as a DateTime value in the Excel workline area:

  • To format the column, first highlight the entire column by clicking on the Column designation, in this case the “C”

  • In the Excel toolbar, click the down arrow of the Cell Format icon to get the formatting menu.  Select “Format Cells” from this menu. (It’s the very last choice.)

  • Excel displays this dialog window:

  • In Category, select Date.  Excel then displays a list of “Types”, scroll down this list and select the entry that corresponds to “mm/dd/yy hh:mm”. Click the OK button

  • The  datetime column is now formatted properly:

 

  • The Date (only) and Time (only) columns export to Excel with no additional formatting required.
Average rating: 0 (0 Votes)

Help us improve the knowledgebase - rate this article above