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

Question

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";

Ganesan C · 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; }

Ganesan C · Answer

Try this. xlWorksheet.Cells[i, datecolm].NumberFormat = "@" ; xlWorksheet.Cells[i, datecolm] = "'" + dt.ToString( "MM/dd/yyyy" );

Ganesan C · Answer

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" ;

Ganesan C · Answer

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

Ranjith Balan · Answer

it worked !!! thanks format is text now

Ranjith Balan · Answer

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 C · Answer

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 Balan · Answer

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 C · Answer

What is value coming from effdate and DateTime.Parse(white1) ?

Ranjith Balan · Answer

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 Balan · Answer

coloumn format changed to text.but date is not coming mm/dd/yyyy coming like 01-01-2015.

Ranjith Balan · Answer

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

Ranjith Balan · Answer

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