Click here to Skip to main content
14,422,439 members
Rate this:
Please Sign up or sign in to vote.
See more:
When I Export Column with Value Contains 04-12-2019 15:35:22 Excel auto change to 12-04-2019 15:35:22
I dont know why excel doing this please help me

What I have tried:

i tried following code while in export
ExcelApp.Cells(j, k + 1) = Format(Date.Parse(frmReportWizard.dgvData.Item(l, i).Value.ToString), "dd/MM/yyyy hh:mm:ss")

ExcelApp.Cells(j, k + 1).NumberFormat = "dd/MM/yyyy HH:mm:ss;@"
Posted
Updated 4 days ago
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Simple: don't use strings. When you use a date in string form, it has to be interpreted and depending on the settings in the system or even teh sheet, it may be incorrect or even invalid. For example is 01/02/03 1st Feb 2003, 2nd Jan 2003, or the 3rd of Feb 2001? The us (and nowhere else) assumes the first, Europe assumes the second, Japan and ISO assumes the third.

Pass the value as a DateTime, and it should "just work".
   
Comments
hareshdgr8 5 days ago
   
ExcelApp.Cells(j, k + 1) = Format(Date.Parse(frmReportWizard.dgvData.Item(l, i).Value), "dd/MM/yyyy hh:mm:ss")
sir i changed this but no effect
OriginalGriff 5 days ago
   
That's still using Format which converts it to a text string ...
Dave Kreskowiak 5 days ago
   
You didn't read OG's answer at all, did you?
hareshdgr8 5 days ago
   
sir is there any other way that you can help sir.. please
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try
ExcelApp.Cells(j, k + 1) = Date.Parse(frmReportWizard.dgvData.Item(l, i).Value)
Of course I am assuming you have set up your datagridview correctly.

Look carefully at what @OriginalGriff is telling you in Solution 1 and the subsequent comment
- Strings should not be used for dates
- The Format[^] function returns a string
- The DateTime.Parse Method [^] returns a DateTime. Interestingly I thought that date.Parse was Javascript not VB.NET

If you absolutely insist on using strings then use the yyyy-MM-dd or yyyy-Mmm-dd format so there is no ambiguity
   
Comments
Richard Deeming 4 days ago
   
Date is VB.NET's alias for the DateTime type. :)
CHill60 4 days ago
   
D'oh! It's been years since I looked at VB.NET in anger and now my memory is going! Must be time to retire :-)
Richard Deeming 4 days ago
   
Other than answering questions here, I haven't looked at VB.NET for years either. But for some reason, my brain seems to hang on to all this useless trivia. :)
hareshdgr8 4 days ago
   
sir did but same issue why its happen dont know help in anotherway if you found any other answer
Richard Deeming 4 days ago
   
Then it sounds like Date.Parse is returning the wrong value. It will probably be related to your computer's regional settings.

You'll have to debug your code and check what it's returning.
hareshdgr8 4 days ago
   
sir i checked but my regional setting is perfect sir if its is not perfect then my othere why my othere date not changed i cant understand help me where i am wrong ... if i enter same with sapec and after that i enter value it take perfect like ExcelApp.Cells(j, k + 1) = " " & Format(Date.Parse(frmReportWizard.dgvData.Item(l, i).Value.ToString), "dd/MM/yyyy hh:mm:ss")
after this excel not change my format why sir i dont know but i found this answer by my own way.. :-)
CHill60 3 days ago
   
You say you have checked your regional setting but did you check the Locale in Excel - it doesn't have to match the regional settings of your computer.
You could also force the format in excel - you haven't said what ExcelApp is but if you're using interop it would be
ExcelApp.Cells(j, k + 1).NumberFormat = "MM/dd/yyyy"
You could also use (E.G.)
ExcelApp.Cells(j, k + 1).Value = Date.Parse(frmReportWizard.dgvData.Item(l, i).Value, New CultureInfo("en-US"))

It's much better to find out what the problem is than to just kludge something together as you have done. Your users will thank you.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Problem Solved By Using TRICK
i just add space so it takes TEXT NOT DATE TIME COLUMN

ExcelApp.Cells(j, k + 1) = " " & Format(Date.Parse(frmReportWizard.dgvData.Item(l, i).Value.ToString), "dd/MM/yyyy hh:mm:ss")


:-) there is no other way that I found
   
v2
Comments
CHill60 3 days ago
   
I hope you don't need to do anything with that date in Excel then

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100