guys, i have a bit of an anomole exporting to excel. on date formats, it seems no matter what i export to excel, it becomes a 24hr clock. for instance the date format thats being exported is stored as:: 03/11/2015 04:26:09 PM
- however when it hits excel, it becomes: 3/11/2015 15:56:09. so, since i'm literally doing nothing to format the incoming data, excel is conveniently making the change for me. it's shows as a 24 hr clock on the rows however, if you click on that cell in that row, in the upper window of excel, it actually shows the data that it was originally. the format of that cell in excel is: m/d/yyyy h:mm - the issue is for my company is other locations are showing some mixed results. i.e.: some of the rows are 24hr and other rows are what the original data was. so, without having to highlight every date column and force the change, is there some setting in Microsoft Office or the Pc itself that can make the default work?
i've added the code that takes a simple datatable to export to excel as an example. i'm pretty sure that there's nothing in this code i can do anything about since all the decisions are being made by excel itself, but it's just for reference.
thanks again

private void exportDT(DataTable myDt)
            DataTable dt = new DataTable();
            dt = myDt;
            string rptName = "Export";
            Response.Buffer = true;
            Response.ContentType = "application/";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AddHeader("Content-Disposition", "attachment;filename=" + rptName + ".xls");
            System.IO.StringWriter sw = new System.IO.StringWriter();
            Response.Charset = "";
            EnableViewState = false;
            string tab = "";
            foreach (DataColumn dc in dt.Columns)
                sw.Write(tab + dc.ColumnName);
                tab = "\t";

            int i;
            foreach (DataRow dr in dt.Rows)

                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                    if (dr[i].ToString() == "")
                        sw.Write(tab + "    ");
                        string replaceWith = "";
                        string myData = dr[i].ToString();
                        string myDReplace = myData.Replace("\r\n", replaceWith).Replace("\n", replaceWith).Replace("\r", replaceWith);
                        sw.Write(tab + myDReplace);

                    tab = "\t";
            System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(sw);


From your program to excel.

the answer to your problem is:
Format the columns of excel

Excel default
value: 3/11/2015 15:56:09 format: (d/M/yyyy HH:mm:ss)

Change it to

Excel custom
value: Nov. 3, 2015 03:56:09 PM format: (MMM. d, yyyy hh:mm:ss AM/PM)


Yellow box for format, change it to custom format
Red box for the default format
Green box for custom format

you can see the custom if you click the More Number Formats... in the yellow box in the image above.

God bless and happy programming.

This article has been dead for over six months. Start a new discussion instead.