1

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?

2
  • Is the data connected? Does it constantly update? – Raystafarian Apr 16 '14 at 14:22
  • Yes, it's connected. I can refresh it with the button in the data tab. – Jeff Sacksteder Apr 16 '14 at 14:36
0

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.

=DATEVALUE([@SnapshotDate2])

This column can then be formatted manually in Excel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.