CODEWITHSUNDEEP
c#exceldate
Bat_Programmer
Bat_Programmer

Reputation: 6851

Unable to convert dates from Excel

I have developed a C# application to read data from Excel files. There are few dates as well which are in the format "dd/MM/yyyy" and I am able to convert them into DateTime objects. However, when I put this same code on the Web server, it reads the Excel date in format "MM/dd/yyyy". I have checked the locale settings on the server and it's same as my local machine which is "en-AU". Below is the function which converts the date:

private DateTime? GetDate(string value)
    {
        if (value.Contains('/') || value.Contains('-'))
        {
            DateTime date = new DateTime();

            string[] formats = { "dd/MM/yyyy", "dd/M/yyyy", "d/M/yyyy", "d/MM/yyyy",
                    "dd/MM/yy", "dd/M/yy", "d/M/yy", "d/MM/yy", '"d-MMM-yyyy", "dd-MMM-yyyy", "d-MMM-yy", "dd-MMM-yy", "MM/dd/yyyy", "M/dd/yyyy", "M/d/yyyy"};

            if (DateTime.TryParseExact(value, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
            {
                return date;
            }
            else
            {
                return null;
            }
        }
        else
        {
            try
            {
                double add = Convert.ToDouble(value);
                DateTime date = DateTime.FromOADate(add);

                return date;
            }
            catch
            {
                return null;
            }
        }

    }

I am using OleDBDataAdapter to read the Excel file into a DataTable object. The function parses all possible date formats, however it always returns null.

What could be the issue?

Upvotes: 1

Views: 487

Answers (1)

ShehanAmarakoon
ShehanAmarakoon

Reputation: 34

Try replacing CultureInfo.InvariantCulture with new CultureInfo("en-AU").

Upvotes: 1

Related Questions