How to set date Column Format to Text in C# interoperability?

Ranjith Balan

Hi, i have to bind mm/dd/yyyy format date in to text format in excel. i used below code.But its still in Custom format how can change this to Text in excel

ie: Any format coming from excel i need to bind mm/dd/yyyy format and i have to change the excel Date coloumn format(any format from excel) to Text.Attached a screen shot

string effdate = xlWorksheet.Cells[i, datecolm].Value.ToString();
var words1 = effdate.Split(' ');
var white1 = words1.FirstOrDefault();

DateTime dt = DateTime.Parse(white1);
xlWorksheet.Cells[i, datecolm] = dt.ToString("MM/dd/yyyy");
xlWorksheet.Cells[i, datecolm].NumberFormat = "MM/DD/YYYY";

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ganesan C5y ago
Accepted answer
Try this.
var words1 = effdate.Split(' ');
var white1 = words1.FirstOrDefault();
xlWorksheet.Cells[i, datecolm].NumberFormat = "@";
xlWorksheet.Cells[i, datecolm] = "'" + AllDateFormat(white1).ToString();

//----------------------------------------------------------------------
        public object AllDateFormat(string effdate)
        {

            string currentdate = effdate;
            CultureInfo provider = new CultureInfo("en-US");
            string[] format = {
                "MM/dd/yyyy", "M/d/yyyy", "M/dd/yyyy","dd/MM/yyyy","dd-MM-yyyy","MM-dd-yyyy",
                "MM/d/yyyy", "M/d/yyyy h:mm:ss tt", "MM/d/yyyy h:mm:ss tt",
                "M/d/yyyy h:mm tt",
                "MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
                "M/d/yyyy hh:mm tt", "M/d/yyyy hh tt",
                "M/d/yyyy h:mm", "M/d/yyyy h:mm",
                "MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm"};
            DateTime dt = DateTime.ParseExact(currentdate, format, provider, DateTimeStyles.None);
            object objvalue = dt.ToString("MM/dd/yyyy").Replace("-", "/");
            return objvalue;
        }
Ranjith Balan5y ago
it worked !!! thanks format is text now
Ranjith Balan5y ago
xlWorksheet.Cells[i, 8].Value.ToString()= 01-01-2015 00:00:00 // in code. 01-01-2015 in sheet

from string effdate = xlWorksheet.Cells[i, 8].Value.ToString(); // getting original excel value-"01-01-2015 00:00:00" in excel sheet its in 01-01-2015 format

whatever date value coming from excel need bind back to mm/dd/yyyy format .But the coloumn format shoud be Text(i need to convert this sheet to pipe delimiated string)
Ganesan C5y ago
is 01-01-2015 expected value from line "xlWorksheet.Cells[i, datecolm].Value.ToString()"?
What is the actual value in the cell and what value coming from above line ?
Ranjith Balan5y ago
effdate= 01-01-2015 00:00:00

white1 = 01-01-2015

DateTime dt = DateTime.Parse(white1) in this line dt is like 01-01-2015 00:00:00
Ganesan C5y ago
What is value coming from effdate and DateTime.Parse(white1) ?
Ranjith Balan5y ago
string effdate = xlWorksheet.Cells[i, 8].Value.ToString();
var words1 = effdate.Split(' ');
var white1 = words1.FirstOrDefault();
DateTime dt = DateTime.Parse(white1);
xlWorksheet.Cells[i, 8].NumberFormat = "@";
xlWorksheet.Cells[i, 8] = "'" + dt.ToString("MM/dd/yyyy");

format is Text but data not in mm/dd/yyyy
Ranjith Balan5y ago
coloumn format changed to text.but date is not coming mm/dd/yyyy coming like 01-01-2015.
Ganesan C5y ago
Try this.
xlWorksheet.Cells[i, datecolm].NumberFormat = "@";
xlWorksheet.Cells[i, datecolm] = "'"+ dt.ToString("MM/dd/yyyy");
Ranjith Balan5y ago
still its the excel column in custom format and data coming like 01-01-2015 not in mm/dd/yyyy.Coloumn format is not changing
Ganesan C5y ago
Hi,

Try this for Text column.
xlWorksheet.Cells[i, datecolm] = "'"+ dt.ToString("MM/dd/yyyy");
//no need below line
xlWorksheet.Cells[i, datecolm].NumberFormat = "MM/DD/YYYY";
Ranjith Balan5y ago
Its not working its giving me 4 digit number only like "4476" i need to bind like in "01/02/2021" in Text column format
Ganesan C5y ago
Hi,

If you want "Text" format instead of "Custom" then, try this.
xlWorksheet.Cells[i, datecolm].NumberFormat = "@";