I'm running into an issue with format of a datetime column when exporting to excel.
What I am trying to do is export datetime column to excel and format as the full datetime on excel, how the various approaches i have tried do not seem to work.
I can get the export to appear to show up as a datetime by:
column.ExcelFormatString = "M/d/yyyy h:mm tt"
column.ExcelExportType = DisplayFormatType.Text;
The problem with this is, that when i put I put a filter on the column in excel, it only sorts alphabetically not by date.
The closest I am able to get behavior wise is this:
column.ExcelFormatString = "M/d/yyyy h:mm"
column.ExcelExportType = DisplayFormatType.Custom;
This allows me to sort by date, but makes the time portion 24 hr format. If I try this with "M/d/yyyy h:mm tt" it ignores the AM/PM indicator and just writes "tt" at the end.
Ideally what I would like is a away to get the datetime to show up in 12 hour format in excel ("M/d/yyyy h:mm tt") while still being able to sort by Earliest/Latest instead of Alphabetically.
Any help would be appreciated, thanks.