Hi All,
I am facing a strange problem, when I import data from excel to vb.net my date field format changes. In excel, date field is "dd-mm-yy" but when i import this cell to vb.net grid
it changes, if the day value is more than the month (ie if day=13 and month=12) then it gives correct data but if the month less than the day (ie if day=11 and month 12) then it changes.

my code is:

dim colD as integer = 4

oXLApp = New Excel.Application 'Create a new instance of Excel

oXLBook = oXLApp.Workbooks.Open(txtFileSource1.Text.ToString , , True, True)

oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet

lastFileSourceIndex = 1

Static breakOut AsBoolean, lastGridRow&, IOFlag$

breakOut = False

lastFileRowIndex = 1

lastGridRow = flvwDTR.Rows - 1

WhileNot breakOut

IOFlag = CStr(oXLSheet.Cells(lastFileRowIndex, colF).Value)

If Len(IOFlag) > 0 Then

atdate = CStr(Format(DateTime.Parse(oXLSheet.Cells(lastFileRowI ndex, colD).value, Globalization.CultureInfo.CreateSpecificCulture("en-CA").DateTimeFormat), "dd/MM/yyyy"))

msgbox (atdate)

lastFileRowIndex = lastFileRowIndex + 1

Else

breakOut = True

EndIf

EndWhile

and the result is

excel file date msgbox result
10-11-08 11-10-2008
09-11-08 11-09-2008

13-11-08 13-11-2008
14-11-08 14-11-2008



Please help on this

thanks in advance

Not strange at all.

Your Excel date is being imported into VB *AS A STRING*. And VB's *DEFAULT* string-to-date translation is USA date format: MM-DD-YYYY. So if the first number is 1 through 12, VB accepts it as a month. It's only when the number is more than 12 that VB says "oh, this must be a day number, so let me change to DD-MM-YYYY".

Now, the CULPRIT is here, of course:
And it *SHOULD* happen that, by specifying the correct Culture (as you are doing), you would get DD-MM-YYYY by default. But...

Perhaps MS thinks that Canadian date format matches USA format??? That is, thinks the "en-CA" defaults to MM-DD-YYYY???

One easy thing to try would be to use "en-UK" instead of "en-CA". See if that changes things.

By the way, you could improve your code's performance quite a bit by *NOT* doing that "lookup by culture name" each and every time through the loop!!!

Assign the culture at the top of the page ONE TIME, to a local variable, and then use the local variable throughout.

Always try to avoid anything that smacks of "lookup by name string" inside of loops.

Just out of curiosity...

Did you try *NOT* parsing the date coming from Excel??? If that field in Excel is *ALREADY* a DateTime value, you might be able to use it as-is.

That is:


Never worked with Excel as an object in .NET, but certainly works this way with Access. If the sheet already knows the value is a date, no transform is needed.

Finally, there was never any reason for your CSTR( ) wrapper, there. Format *does* product a String. Always.

Okay, one more comment...

Your code could be more efficient and smaller.

Thus:

Hi Old Pedant,
Thanks for the quick and detailed reply,

Unfortunately I already tried both the suggetion you given , but no luck

ie : atdate = Format(oXLSheet.Cells(lastFileRowIndex, colD).value,"dd/MM/yyyy")
atdate=CStr(Format(DateTime.Parse(oXLSheet.Cells(lastFileRowI ndex, colD).value, Globalization.CultureInfo.CreateSpecificCulture("en-UK").DateTimeFormat), "dd/MM/yyyy"))


Kindly help to resolve this issue

Kind of out of ideas. It looks like the (incorrect) conversion is taking place inside the Excel.Application DLL code, before it ever gets to your code.

You may need to see if there is some way to tell Excel.Application to treat your dates as dd/mm/yyyy format.

You *might* try a bit of debugging, though I tend to think it's going to just confirm what you already know.

For example: