I have an Excel document with a table linked to a table in sql server containing a datetime field. The datetimes display as yyyy-mm-dd hh:mm:ss.sss and I'm unable to format the cells directly to a different format. Presumably, this is because the table definition overrides the cell format. Table and cell styles appear to only modify color and style, not formatting. How do I change an existing table?
This is usually due to the specific Date datatype returned by SQL, which sounds like it is DATETIME2. DATETIME and SMALLDATETIME get formatted as a Custom format e.g. d/mm/yyyy h:mm (on an Australian machine).
If your Data Connection uses a Query, you could change the SQL code to use the CAST function to convert the date column to DATETIME, which should produce the desired result.
Alternatively you can add a column to the Excel table with a formula e.g.
This column can then be formatted manually in Excel.