Formatting issue of datetime while exporting to Excel

Question

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.

What is your `item.requestedDate` value exactly and what is your current culture? — Soner Gönül, Apr 04 '14 at 06:48
its value is 04/03/2014 05:05:19 PM .I want to display as same — Praveen VR, Apr 04 '14 at 06:48
Hello sir, i also face same problem. if you get answer , please write here — sanjay bhansali, Jul 30 '14 at 09:01

score 2 · Answer 1 · edited May 23 '17 at 12:24

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.

edited May 23 '17 at 12:24

Community

1
1

answered Apr 04 '14 at 07:21

Joe

118,426
28
194
329

Kidquick · Answer 2 · 2015-11-27T19:38:47.873

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") + "\"");

edited Nov 27 '15 at 19:38

answered Nov 25 '15 at 21:19

Kidquick

1,092
11
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

Formatting issue of datetime while exporting to Excel

2 Answers2