2

I am doing Export to Excel process in my asp.net application.

In that process, I am writting the DateTime value to excel using the below code:

string reqDate = item.requestedDate.ToString("MM/dd/yyyy hh:mm:ss tt");
Response.Write(reqDate + "\t");

But the result in the excel file missing the second part in the datetime like below:

4/3/2014 17:05

I want it look like 04/03/2014 05:05:19 PM

How to do this?

Please help.

Praveen VR
  • 1,554
  • 2
  • 16
  • 34

3 Answers3

2

It looks like you're exporting to CSV, not Excel. Excel reads the CSV, recognizes a DateTime, and formats it using its default culture-specific format.

If you want to force it to be formatted as text, look at the answer to this question.

Alternatively you could consider streaming a genuine Excel file, generated using a tool such as EPPlus or Aspose Cells, rather than CSV.

Community
  • 1
  • 1
Joe
  • 122,218
  • 32
  • 205
  • 338
2

This issue is caused by how Excel parses DateTime's in CSV documents. More specifically, if the DateTime's Day property is less than or equal to 12.

In your example (04/03/2014) Day <= 12, and Excel formats it in a way you don't want. However for DateTime's with Day > 12 (e.g. 04/23/2014) Excel formats it as you'd expect.

A trick for getting DateTime's to display as you want in Excel is to wrap them in an Excel formula like this: ="04/03/2014 05:05:19 PM"

if (item.requestedDate.Day <= 12)
    Response.Write("=\"" + item.requestedDate.ToString("MM/dd/yyyy hh:mm:ss tt") + "\"");
Kidquick
  • 1,102
  • 12
  • 13
  • In this example, is it not changing the data type to custom (string)? So sorting by date would fail. I encountered this with strings that begin with 0's and Excel assumed integer. Adding ' + value output it correctly but still sorts incorrectly. – Anthony Mason Feb 03 '17 at 14:31
  • 1
    @Anthony Yes the output is a string literal, which could lead to unexpected sorting. A workaround could be to use a "most to least significant" format such as "yyyy/MM/dd hh:mm:ss tt". – Kidquick Feb 03 '17 at 16:58
0

Thanks! That tricking for getting DateTime's by Excel formula works!

And it works with milliseconds! Works without any cell formating!!!

My C# example for Excel export data:

x_sheet.Cells[your_row_id, your_column_id] = "=\"" + your_object.ToString("MM/dd/yyyy hh:mm:ss.fff") + "\"";

Result at excel cell: ="08.19.2022 09:58:05.933"

Excel printscreen

I looked for that so long....

Thank You very much!

shados250
  • 1
  • 1
  • So basically "Thanks" is what your post is saying. Right? – Yunnosch Sep 26 '22 at 23:36
  • Please don't add "thank you" as an answer. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation), you will be able to [vote up questions and answers](https://stackoverflow.com/help/privileges/vote-up) that you found helpful. - [From Review](/review/late-answers/32810003) – zolty13 Oct 02 '22 at 16:23