Microsoft Excel - Forcing International date format

Hello

I would like to print a chart with current date in the header. In my VBA code I use the following code:

chrt.PageSetup.RightHeader = "&iPrinted:&i " + Format(Now, "[$-409]mmmm d, yyyy")

to get this date format: July 16, 2010

Unfortunately I get my local (Danish) format, e.g. with the month in Danish ("juli") instead of US-English, which the code [$-409] should set.
If I manually set the format of a random cell, using the above format code, I get the wanted result, with the only difference that I need to write the format using the danish abbriviations for day, month and year. Doing that in the VBA code does not work (How I love that function names and format codes are translated...)

Please help - want am I doing wrong?

\Christer

i tested your code, it appeared to work correctly on my system aus/eng

i don't  believe you need to specify the code page at all, just the format string

can't help beyond that

Hello Peter

Thanks for your response.

Whether you need the local ID code or not normally depends on the Primary Editing Language you set. In my case I do need it.

Since I wrote my question, I thought of trying to use the Macro recorder to set the format and language of a random cell. The result was exactly like the code I'm using, so my conclusion is, that the Danish edition has some bugs...

For now I'm keeping the local ID in my code, hoping that some day it'll work (and I'll probably write MS about the issue...)

\Christer

i believe you would have to write your own function to replace the danish with the english

if you are only converting long month to one language it would be very simple

you can try

range("a14") = 14/11/2025

chrt.PageSetup.RightHeader = "&iPrinted:&i "& Application.Text(Range("a14"), "mmmm d, yyyy")

chrt.PageSetup.RightHeader = "&iPrinted:&i " & Application.Text(Range("a14"), "[$-409]mmmm d, yyyy")

use any cell to suit

Unfortunately, what you suggest did not work for me, but it did give me the idea to put the date in a cell, format the cell, and then copy it back to the header string. This IS a workaround to a bug in the Danish version, but it works:

.Cells(x,y) = Format(Range("A14"), "mm-dd-yyyy")

.Cells(x,y).NumberFormat = "[$-409]mmmm dd, yyyy"

chrt.PageSetup.RightHeader = .Cells(x,y).text

.Cells(x,y).ClearContents

Thanks a lot for helping me Peter!

but it did give me the idea to put the date in a cell, format the cell, and then copy it back

i think that was what i was suggesting

This IS a workaround to a bug in the Danish version, but it works:

i do not think it is a bug, or specific to the danish version

visual basic 6 (and equivalent vba versions) has no provision to format date in any but the default language, apart from a user function to do translation, or some control or object (eg. cell in this case) that has provision to do translation

it would not be hard to write a translation function, but in the case of wanting to specify a choice of language would require knowledge of all the languages concerned

maybe i can try it with google translation, if i get some spare time