1

I think I have read just about every post on this topic and none of the proposed solutions works in my case so here goes.

I am using CF9 (upgrade not an option) for this project. I query a date field from a MSSQL database and use spreadsheetAddRows() to put the results into a spreadsheet (xls or xlsx, same result either way).

The date shows in excel as 2020-05-11 00:00:00.0 and isn't recognised as a date so the date formatting doesn't work.

I have tried using SpreadsheetFormatColumn (s, { dataformat="d-mmm-yy" }, 2); but this doesn't format the date either and has the exact same result in excel.

I have tried many variations of selecting convert(varchar, datecolumn, 101) from the database but these always just end up as text fields in excel as well so again, no date formatting and they sort in the wrong order.

Can anyone tell me what the correct format for a date is for CFSpreadsheet so that excel actually recognises it as a date?

Miguel-F
  • 13,450
  • 6
  • 38
  • 63
Jay2001
  • 59
  • 7
  • I suspect that Excel reads the local machine's settings for date and time formats. `yyyy-mm-dd` will sort properly if you have flexibility in that matter. – Dan Bracuk May 15 '20 at 20:18
  • ISO format will sort correctly, but Excel still doesn't recognise it as a date and my boss wants dd-mmm-yy format! – Jay2001 May 15 '20 at 23:41
  • Which posts did you try? Also what's your full version? IIRC there were a bunch of bugs in CF9 and 10. Even across updates. – SOS May 16 '20 at 10:10
  • Does this answer your question? [ColdFusion: cfspreadsheet localized date format](https://stackoverflow.com/questions/19588590/coldfusion-cfspreadsheet-localized-date-format) – Dan Bracuk May 16 '20 at 12:57

0 Answers0