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