Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format problems when used in different countries
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format problems when used in different countries
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" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format problems when used in different countries
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format problems when used in different countries
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" wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format problems when used in different countries
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing date format in a footer | Excel Discussion (Misc queries) | |||
Date format Question | Excel Discussion (Misc queries) | |||
format date in excel | Excel Discussion (Misc queries) | |||
Why Does Date Format Change on Chart | Excel Discussion (Misc queries) | |||
Excel enters date as a text format | Excel Discussion (Misc queries) |