DateTime problem when day <= 12

Question

I looked around a lot and did not write a terrible piece of code for string manipulation, I would like to ask if anyone knows a good way to sort it:

I have a bunch of date strings in the cells that I am pulling, for example:

05/03/2011
05/27/2011
05/31/2011
05/03/2011
05/09/2011
05/31/2011
and etc.

While I am reading any comments where the day can be interpreted as a month, i.e. records 1, 4 and 5 above - it is entered as a DateTime with the replacement of the day and month.

For example, 05/03/2011 reads as DateTime "03/05/2011 00:00:00" The rest read and beautifully provided me with a simple string "05/27/2011".

I get this information from Excel using

 ((Excel.Range)worksheet.Cells[rowCount, 3]).Value.ToString() 

If I try Value2, as in my other lines, it reads these odd dates, such as "40607", but reads the other dates again in normal mode.

Answer 1

If you use the DateTime.ParseExact function to convert a string to a DateTime object, you can specify the specific format your date uses (which looks like "day / month / year"), without any string manipulation.

Example:

 var dateString = "03/05/2011"; var format = "dd/MM/yyyy"; var date = DateTime.ParseExact(dateString, format, CultureInfo.InvariantCulture); 

More information on date and time format strings can be found here .

---

EDIT: Try using the DateTime.FromOADate method to convert the value returned by the Range.Value2 DateTime object, for example something like this:

 var dateTime = DateTime.FromOADate(((Excel.Range)worksheet.Cells[rowCount, 3]).Value2); 

Answer 2

The DateTime.ParseExact Method converts the specified string representation of the date and time into its DateTime equivalent, using the specified format and format information for a specific culture,

The format of the string representation must exactly match the specified format.

 String dateString = "15/06/2008"; String format = "dd/MM/yyyy"; DateTime result = DateTime.ParseExact(dateString, format, CultureInfo.InvariantCulture); 

Answer 3

This seems like a localization issue. Try customizing your language implicitly. For example, in a WPF application, this is something like:

 System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); 

Answer 4

I have a bunch of date strings in the cells that I am pulling, for example:

No no. You have a combination of strings that look like dates and that look like strings . This is an Excel issue, not a C # issue.

Not sure if you are creating a spreadsheet or getting it from somewhere else. But the problem is that Excel is trying to parse the text as it is being entered into the cell. In this case, he makes the wrong decisions about the dates that he finds.

If you enter a date like "03/05/2011", Excel (incorrectly) will analyze it as March 5, 2011 and save it as a numeric date code (40607). Then it applies the date formatting to the cell (it uses m / d / yyyy on my machine).

If you enter a date like 05/31/2011, Excel will not be able to parse it as a date and save it as text.

To prove this, select the cells and go to Edit> Clear> Formats. All the "bad dates" will simply be displayed as numbers, everything else will look like dates.

You have several options:

• Correct the data before entering it into Excel (add everything with "so that everything is entered as text", or do not forget to create a spreadsheet on the machine with the correct date settings.)
• Do not use .Value.ToString() from Excel, just use .Text . This will ignore the bad parsing that Excel did, and should give you a consistent text value (of both types) that you can ParseExact with C # for other answers.

(2) much simpler, and if spreadsheets already exist, may be your only choice.

Answer 5

The problem is that your Dates read like American culture or something like that.

If you use the following, you can specify the format in which you expect the date: use

 DateTime result; if(DateTime.TryParseExact("dd/MM/yyyy", out result)) { // Got an English date }