DateTime problem when day <= 12

Question

I've looked around a lot and short of writing a horrible chunk of code to manipulate the string, I'd like to ask if anyone knows a nice way of sorting it:

I have a bunch of date strings in cells that I'm pulling out such as:

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

While I'm reading any entires where the day can be construed as a month - i.e. entries 1, 4 and 5 above - it gets put in as a DateTime with the day and month swapped.

For example, 03/05/2011 gets read in as a DateTime "05/03/2011 00:00:00" The others are all read and nicely provide me with a simple string of "27/05/2011".

I'm getting this info from Excel, using

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

If I try Value2 as with my other lines, it reads those odd dates as things like "40607" but again, will read the other dates normally.

Answer 1

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

Example:

var dateString = "03/05/2011";

var format = "dd/MM/yyyy";

var date = DateTime.ParseExact(dateString, format, CultureInfo.InvariantCulture);

More information on custom 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 property to a DateTime object, e.g. something like this:

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

Answer 2

DateTime.ParseExact Method converts the specified string representation of a date and time to its DateTime equivalent using the specified format and culture-specific format information.

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

String dateString = "15/06/2008";
String format = "dd/MM/yyyy";

DateTime result = 
   DateTime.ParseExact(dateString, format, CultureInfo.InvariantCulture);