UK versus United States English

[Guest]

I have a template for English (United States). A customer is in the U.K. and
has his regional settings set to U.K. and office language set to English
(United States). The customer uses our program to export data into a
template.

Now, this template has a column with dates. The problem is that the dates
show up in the format dd-mm-yyyy. However the formatting show in the
Format::Cells option shows it set to "Custom, mmm-yyyy". The user has
formula's that need this mmm-yyyy format. I have tried many times to get this
to show the correct format, but still don't have it working.

The best solution I've had so far was to create a macro that went through
each cell in the column and called Format( "mmm-yyyy"). And, although this
solution works on my PC, it does not apparently work for the customer.

Does anyone have any ideas of how to fix such a thing?

 

[Fred Smith]

I don't think your problem is the formatting. Formulas deal with cell contents
regardless of the format. In other words, in an Excel formula, a date is a date
regardless of how it's formatted.

It looks to me like you don't really have dates -- you have text that looks like
a date. Could this be the problem? What happens when you try to reformat the
cell? Does it actually change format or not?

 

[Guest]

Thank you for your suggestion Fred.

I am using formula's, but formula's are not working when they reference
those cells with bad formatting. For example

Cell C8 displays 30-06-2004. Its' formatting is Custom mmm-yyyy.
Cell B8 has the following formula: if(Month($C8)=12, Year($C8), "")

So, logically, B8 would show nothing. However, it instead shows ###. And,
even when we hit a date that is on month 12, it still shows ###.

So, it still appears to me that there is some strange formatting problem.

 

[Guest]

To answer your question

1) What happens when you try to reformat the cell?
According to the Format::Cells window, the formatting is changed.
However, the 30-06-2004 is still displayed to the user. I tried changing the
format to Number, but the date is still displayed.

 

[Fred Smith]

That means you have text, not an Excel date. You need to force it to change into
a date. Edit the cell (with F2, then enter), and it should change into the date
format you want.

 

[Fred Smith]

A result of '###' does not indicate bad formatting. It simply means the cell is
not wide enough.

Widen the column, and see if that solves the problem. A simple way to ensure the
column is wide enough to display all results is to position the cursor on the
right side of the column border until the cursor turns into a double headed
arrow. Then double-click.

 

[Guest]

Thanks Fred,

I tried this, but the problem is that it does not work when the customer
runs my macro.

 

[Fred Smith]

No problem. When you're looking for a solution, rather than just lamenting 'woe
is me', post back.