asked on

cfspreadsheet date formatting

We are generating a new spreadsheet using cfspreadsheet. We start with an Excel template, copy it, and then populate the data. The dates output into a date format column and they LOOK like they are dates but they are left aligned and when you apply the filter button they aren't grouping by year so Excel is definitely not seeing them as dates even though they look like dates. The client really wants to be able to filter properly. Even resetting them as dates doesn't change the filter option - you have to retype them in as dates.

I have tried

spreadsheetSetCellValue(sObj1,dateformat(row.P_CommentsIssued, 'm/d/yyyy'),currentrow,15);

Open in new window

and

spreadsheetSetCellValue(sObj1,dateformat(row.P_CommentsIssued, 'mm/d/yyyy'),currentrow,15);

Open in new window

But neither of those things have worked.

Any suggestions?

Rob Henson

Sounds like the output source is sending the date as a text string that looks like a date.

digitalwise

ASKER

Just in case anyone is curious -

spreadsheetSetCellValue(sObj1,dateformat(row.P_CommentsIssued, 'm/d/yyyy'),currentrow,15,'date');

Open in new window

ASKER CERTIFIED SOLUTION

digitalwise

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

SpreadsheetSetCellValue(spreadsheetObj, value, row, column, datatype)

Open in new window

Thanks for posting your solution! Took me a minute to spot what was different ;-) So glad Adobe eventually added a cell "datatype" because spreadsheet date handling was sheer torture before that ...

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetsetcellvalue.html