VBA & Dates

[NEveritt]

Got a strange one here.

the person next to me is having problems with dates on his PC. It is manifesting itself in a few ways, I will list them here. The same problems do not appear for me when i run the same code.

Just to confirm, his regional settings are set as English UK.

1) In excel, bringing data back from a SQL database, for a date held on the excel workbook. It finds the correct data, i.e. for that date, when you step through the code, just before it prints the date into excel, if you hover the mouse over the date it is in the correct dd/mm/yyyy format, and also if you debug.print it is in the correct format.

However when displayed in excel it has now changed to USA mm/dd/yyyy format.

2) He has created some imports in excel that he will import into a sql database. The data is all in dd/mm/yyyy format and correct in Excel. When he imports the data into sql, it switches the data into mm/dd/yyyy format.

If he makes the import file a csv it works ok.

3) Lastly, and possibly not connected but worth mentioning, there is a simple asp webpage, that entries are made into, after selecting a date via a calendar, and they are inserted into the same sql database. again when it inserts, it switches the date format round.

If I do this, there is no problem.

Does anyone have any ideas about what is happening here?

 

[SkipVought]

Hi,

The key word in your post was Excel displays the date in American format.

It's Microsoft in Seattle Washington, United States of America.

Merely format the column of dates as you wish and be done with it. The important thing is that the VALUE has apparently not been compromised.

Skip,
[sub]
[red][/red]
[/sub]

 

[NEveritt]

Skip, I am afraid it has changed the value, the serial date of the value has now changed (for example the date we are working with is 05/06/2007 and it is displayed as 06/05/2007) so the serial date is for the 6th May 2007

 

[SkipVought]

What happens if you FORMAT that cell as mmm?

Skip,
[sub]
[red][/red]
[/sub]

 

[NEveritt]

i have to make this clear to all that read this post to clear this up:

the serial date of the cell is showing as the serial date for 6th May 2007 (i.e. change the format to general and it shows the serial date), when the data imported was clearly for 5th June 2007

It is not a formatting issue, there is something influencing this install of Excel (and its not the regional settings)

 

[PHV]

In excel, bringing data back from a SQL database
Why not using a non ambiguous format like yyyy-mm-dd for the import ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

It is a STRING to DATE conversion issue.

Try entering 5/6/2007 (5th June 2007) in a cell. I'm guessing that Excel parses the string as 6 May 2007, dispite regional settings.

As PHV suggested, use a Non-Ambiguous date format.


Skip,
[sub]
[red][/red]
[/sub]

 

[NEveritt]

Thanks for that PHV, if this was a new project I would consider that, however everything that is linked to the database would have to be re-written, and thats a lot of things.

There has to be a system setting infuencing this, and I need to find that, if it works for me, worked on the my old PC that i had 2 weeks ago, I do not know why it doesnt work for this one user.

I think I will ask for a reinstall of his Excel first, then if that doesnt work, a resintall of XP????

 

[NEveritt]

Skip it doesnt, if you enter into excel it works fine, its when I am importing into excel from sql.

But as I said, in the vba code, in the point before if puts it into the spreadsheet, it is in the correct format, the second it enters it, its in the wrong format.

 

[SkipVought]

Check the ODBC Driver Configuration to see if there's some setting for date conversion or something.

Skip,
[sub]
[red][/red]
[/sub]