+ Reply to Thread
Results 1 to 5 of 5

Date format problems when used in different countries

  1. #1
    Fred
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Fred
    Guest

    Re: 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. #4
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #5
    Fred
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1