VBA switching month/dates

[himam275]

Hi,

I have written a VBA program that allows user to pick a date using a DTpicker control BUT when I write the values from the interface to the excel cell, the month and dates are switched i.e. 5/1/2012 (Jan 05, 2012) will read as (May 01, 2012). In the code, I am formatting the data to dd/mm/yyyy format BUT still the month and day get switched.

Any ideas?

Thanks in advance

[Don]

In what country are you located? Or, for what country is your version of Excel?

[Sisyphus]

Hi,
Date and time formats are set in the Regional Settings that you can access through the Control Panel.

With that said, you should realize that the topic you have raised is a tricky one because - here I am quoting from something I read recently, don't recall exactly where - Excel is very international in its outlook but VBA is purely American. So, Excel will try to give you a date in your country's language and will look at your Windows settings and, possibly, also its own ambitions to reach that end. VBA, on the other hand, is strictly into mm/dd/yy. Conflict is bound to happen, and it does.

The thing to do is to convert the date you get from your DTpicker to something that you can control as early as possible. That guy recommended a string. The trick is that Excel can convert a string into a date which, being of its own making, it can interpret correctly thereafter.

If the above doesn't suffice for you to solve the problem, please post your workbook with the DTpicker and relevant code.

[himam275]

I am in US and the program that I have written is for Canada. The program works fine at my end and I do not get any issues but my Canadian conterpart is having these date issues

[Sisyphus]

Hi,
Without knowing the date settings in your Regional Settings, and those of your Canadian customer, no further assistance will be possible. Don also asked for the country where the respective Excel versions were sold and which language they are set to use. The error you get seems to indicate that one of your machines is set to dd mm yy while the other works on mm dd yy, but I am not going to guess while you don't even want to disclose the settings on the machine in front of you.

Once the problem has been identified its cure will be in the code. Are you able to post the relevant part? We need to look at the how you read the date from the DTpicker and how you write it to the cell.

[himam275]

Your right, my excel setting is in mm/dd/yyyy format whereas my counterpart in Canada setting is dd/mm/yyyy

The file itself is quite large (around 10MB). Here is the code where I am reading from the DTPicker and writing to the excel

sDepDate = frmPay.dtDep.Value 'Read date from DTPicker

Sheet2.Cells(k, nDatDep) = sDepDate ' Write date to Excel cell
Sheet2.Cells(k, nDatDep).NumberFormat = "dd/mm/yyyy" ' force format to Canadian requirements

I have define the variable "sDepDate" as string. Will defining that as Date help?

[Don]

Formatting it as a date might help. But, as a string, you can always use the string manipulation functions in VBA to change the date around within the macro before outputting it.

[Sisyphus]

Hi,
Your question shows that you are on the right track. To solve the problem I need to try things out. So, why don't you go firs? But thinking ahead may help:-

You are taking a String from the DatePicker. Whenever you convert this String to a Date the conversion will be done according to the rules of the local Excel. On your machine the month will be placed in first position, on your customer's in second. Then you force a reversal. It's an even bet that this is what causes the upset.
The String taken from the date picker is correct, though different, on both machines. Your code that manipulates it must be capable of turning out the desired result regardless of where the month is in the String.

Indeed, you may read the date from the date picker as a date, too. The only thing to remember is to let Excel do the presentation and don't interfere with that unless you can take full control, which you can't if you have a Date. You may leave everything to Excel. Format the date as "Short Date" to use the Regional Settings of the machine where your program runs. If 40397 is Jan 1, 2012 as taken from the date picker (as a Date) Excel will show day, month and year in correct sequence on any installation, anywhere, but not necessarily as you have imagined.

[himam275]

Good suggestions. Thanks a lot. Let me try it and see how it goes and report back.

[himam275]

Hi,

I made some changes to the code and they seem to be working so far. What I did was instead of storing the date from the DTPicker to a variable and writing it to the excel cell, I am directly writing the cell from DTPicker like below

Instead of this in previous code:
sDepDate = frmPay.dtDep.Value 'Read date from DTPicker

Sheet2.Cells(k, nDatDep) = sDepDate ' Write date to Excel cell
Sheet2.Cells(k, nDatDep).NumberFormat = "dd/mm/yyyy" ' force format to Canadian requirements

I did following:

Sheet2.Cells(k, nDatDep) = frmPay.dtDep.Value

I have an additional loop to see if the date is changed after putting into excel cell BUT so far its coming ok directly.

Thanks for all your support

Regards