Results 1 to 1 of 1

Thread: Date Format Issue When Regional Language Setting Changes And You've Used TEXT Formula

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Date Format Issue When Regional Language Setting Changes And You've Used TEXT Formula

    I recently came across a situation where one of the Excel files had to be used by people in various countries. And it so happened that their regional language settings were completely different. One was English. And the other was another. The formula that I was using was to display the date from a particular cell in another format.

    So I had 12th January 2015 as 1/12/15 in cell A1.

    And =TEXT(A1,"dd mmm yyyyy") in B1. In the English setting this would result to 12 Jan 2015

    But when you view this in another language setting, like German for example, you get your result as dd 00 yyyy

    Even worse, if you use =TEXT(A1,"dd/mmm/yyyyy"), that will throw an error in non English settings.

    Anyway, as a work around, I used the GET.CELL feature explained in http://www.excelfox.com/forum/f13/ex...s-get-cell-75/

    Attaching a sample for reference. Pretty cool, huh!

    Note: The GET. features of XLM (Excel 4.0 Macro) requires a macro enabled file.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 2
    Last Post: 09-06-2014, 09:38 AM
  2. Date COlumn AutoFiltering Issue!
    By labkhand in forum Excel Help
    Replies: 3
    Last Post: 11-06-2013, 01:41 AM
  3. Replies: 5
    Last Post: 06-15-2013, 12:40 PM
  4. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  5. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 AM

Posting Permissions

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