Date format problems when used in different countries

[Fred]

Based in the UK and using Excel/97 I have the following formula,
=Start!E12 & " / Date: " & TEXT(Start!E8,"dd-mmm-yyyy"). This
evaluates correctly to show the name of the person creating the
spreadsheet (from Start!E12) and the date it was created (from
Start!E8), however a colleague based in Zurich, using Excel/2000 only
sees the name of the person and the date displays as dd-00-yyyy.

After much digging, we finally found that we needed to set the
formatting to TT-MMM-JJJJ as German for Day = Tag and for Year = Jahr.
I am now left with the problem of having to find a solution for
multiple nationalities as this spreadsheet will be used in UK, USA,
Germany, Switzerland, Italy and Spain, all of whom have their own words
for Day, Month and Year.

Can anyone offer me an idea of where to start with this one, formula of
VB, I don't mind, just so long as I don't have to maintain 4 or more
versions of the spreadsheet. Is there a way, perhaps, of finding what
the Language setting is and then coding accordingly ? I really am open
to suggestions.

Regards
Fred

 

[Bob Phillips]

Hi Fred,

I would use a UDF, as VBA works in one format


Function MyDate(rng As Range, dte As Range, dateformat As String)
MyDate = rng.Value & " / Date: " & Format(dte, dateformat)
End Function

and use like so

=mydate(Start!E12 ,Start!E8,"dd mmm yyyy")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Fred]

Hi Bob,

Thanks for the swift response. I'm not a fluent VB speaker and have
never used a "written" function before. I created a separate "module"
with the Function code in, changed the formula to call MyDate and then
hit Calculate, it responded immediately with a Compile Error/Syntax
Error, highlighting the MyDate = rng.Value statement.

UDF = Universal Date Format ?

Thanks again
Fred

 

[Bob Phillips]

Fred,

UDF - User Defined Function.

I can't tell at long distance why it would fail. Can you mail me the
workbook?

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Fred]

Hi Bob,

I found the cause, the tab character had been converted into some
unknown character and VB threw a fit. I've removed this and it all
works perfectly.

Thanks for your time and assistance
Regards
Fred