Date Format from excel string

Question

0.00/5 (No votes)

See more:

Hi,

I am trying to read an excel, which has a date field. But the problem is that some files may have date in dd.MM.yyyy hh:mm format where as some may have in MM.dd.yyyy hh:mm format. So while converting this to DateTime throws error. How can i handle it???

string[] formats = new[] { "MM.dd.yyyy hh:mm", "dd.MM.yyyy hh:mm", "MM/dd/yyyy hh:mm", "dd/MM/yyyy hh:mm", };
foreach (string format in formats)
{
    try
    {
        _ChangedDate = DateTime.ParseExact(dr[ConfigurationManager.AppSettings["ChangedDate"]].ToString(), format,CultureInfo.InvariantCulture);
         break;
    }
    catch
    {
    }
}

Currently i have handled like this which is very cheap and crooked. so anyone help me out

Posted 26-Mar-14 6:37am

Arjun Menon U.K

Updated 26-Mar-14 6:55am

Richard C Bishop

v2

Add a Solution

Comments

Philippe Mori 26-Mar-14 13:10pm

You have to use a format that does not depends on the culture or be consistant with the Culture that is used accross application.

The above liste contains ambiguities as 03.04.2014 would be valid for 2 of the format in the list while date like 13.04.2014 or 03.14.2014 could be interpreted in a single way.

If you are using files generated by Excel, then you should open them using OpenXML, Office Automation (local use if you are sure the client have adequate version of Excel) or a third party Library so that you don't have to bother with such détails.

I would think that if the data is transfered by the clipboard, then you can probably specify a format like XML that would not have such ambiguities.

Finally, it is also possible that in your application, you let the user select which format to use or simply assume that the user use the system format accros the system (which might not be the case particulary if available locations and languages differs across applications (and OS) and you use one that is not available everywhere.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)