I have an excel column having date. That date may be diffrent format like 31-05-1989,1/2/1989 .I want to take that date and given in a constant format like "MM/dd/yyyy" and i have to bind / write back to excel.Then the excel column should be in text format but the column should display MM/dd/yyyy only.I have tried and failed for every format.I will get different format but i need to give back mm/dd/yyyy.Following code will work only one format from excel(if excel format is Text) how can i make a generic methode which will accept any date format and write back to MM/DD/YYYY
string effdate = xlWorksheet.Cells[i, datecolmn].Value.ToString(); //failing when i get a different format other than Text xlWorksheet.Cells[i, datecolmn] = AllDateFormat(effdate); xlWorksheet.Cells[i, datecolmn].NumberFormat = "MM/DD/YYYY";
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", "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"}; object objvalue = DateTime.ParseExact(currentdate, format, provider, DateTimeStyles.None).ToString("MM/dd/yyyy").Replace("-", "/"); return objvalue; }