Sujeet Raman

Sujeet Raman

  • 752
  • 927
  • 354.1k

How to Change Date format in Excel column using interoperability in c#

Sep 1 2021 6:51 PM

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


Answers (3)