Date time column issue while exporting to excel in VB.NET

Question

0.00/5 (No votes)

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 14-Jan-20 1:03am

hareshdgr8

Updated 15-Jan-20 18:36pm

v2

Add a Solution

3 solutions

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".

Posted 14-Jan-20 1:16am

OriginalGriff

Comments

hareshdgr8 14-Jan-20 7:59am

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 14-Jan-20 8:04am

That's still using Format which converts it to a text string ...

Dave Kreskowiak 14-Jan-20 8:09am

You didn't read OG's answer at all, did you?

hareshdgr8 14-Jan-20 8:05am

sir is there any other way that you can help sir.. please

Solution 2

Try

C#

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

Posted 14-Jan-20 2:31am

CHill60

Comments

Richard Deeming 15-Jan-20 9:19am

Date is VB.NET's alias for the DateTime type. :)

CHill60 15-Jan-20 9:21am

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 15-Jan-20 9:25am

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 15-Jan-20 9:28am

sir did but same issue why its happen dont know help in anotherway if you found any other answer

Richard Deeming 15-Jan-20 9:34am

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 15-Jan-20 9:37am

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 16-Jan-20 8:56am

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

hareshdgr8 · Accepted Answer · 2020-01-15T18:37:00

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

Posted 15-Jan-20 18:37pm

hareshdgr8

Updated 15-Jan-20 18:38pm

v2

Comments

CHill60 16-Jan-20 8:37am

I hope you don't need to do anything with that date in Excel then