CODEWITHSUNDEEP
c#datetime
Aidan
Aidan

Reputation: 61

DateTime problem when day <= 12

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.

Upvotes: 6

Views: 2715

Answers (5)

BradC
BradC

Reputation: 39916

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

No, you don't. You have a mix of strings that look like dates and dates that look like strings. This is an Excel issue, not a C# issue.

Not sure if you are creating the spreadsheet, or if you are getting it from somewhere else. But it the problem is that Excel attempt to parse text as it is entered in the cell. In this case, it is making some wrong decisions about the dates it finds.

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

If you enter a date like "31/05/2011", Excel can't parse it as a date, and it stores it as text.

To prove this, select the cells and go to Edit > Clear > Formats. All the "bad dates" will just show as numbers, all the rest will stay looking like dates.

You have a few choices:

  1. Fix the data before its entered into Excel (prepend everything with a ' so its all entered as text, or make sure to create the spreadsheet on a machine that has the right date settings.)
  2. Don't use the .Value.ToString() from Excel, just use .Text. This will ignore the bad parsing that Excel did, and should give you a consistent text value (from both types) that you can ParseExact with C#, per the other answers.

(2) is a lot easier, and if the spreadsheets already exist, may be your only choice.

Upvotes: 1

Donut
Donut

Reputation: 112815

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

Upvotes: 8

Akram Shahda
Akram Shahda

Reputation: 14781

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

Upvotes: 2

OsQu
OsQu

Reputation: 1058

That sounds like a localization problem. Try setting your locale implicititly. For example in WPF application it's something like:

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

Upvotes: 1

Ian
Ian

Reputation: 34489

The problem is because your Dates are being read as american culture or similar.

If you use the following you can specify the format you expect your dates to be in:use

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

Upvotes: 0

Related Questions