Forum Discussion

Barbara Floyd's avatar
Barbara Floyd
Copper Contributor
Nov 06, 2017

Date format conversion USA / UK

I am in the Uk and enter a date in USA format into a cell of type ‘general’ e.g. for 11-FEB-2017 I enter 02/11/2017. I save the file and email it to USA. When opened in the USA excel has changed the format of the cell to ‘date’ and has Interchanged the month and day. So it reads as 11/02/2017 which is interpretered in America as 02-Nov-2017. How can I stop this from happening? Note that the spreadsheet is sent to multiple people so I can’t rely on changing local settings on my Excel, it has to be something in the spreadsheet itself which forces what was Input to be displayed. Thanks in advance for any help!
excel

6 Replies

Sort By
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Nov 06, 2017

    Hi Barbara,

     

    First and foremost, you have to know that the date format in Excel is based on the date setting in Control Panel of your OS.

     

    I think that the date format in your OS is: (dd/mm/yyyy), since you live in the UK.

    So, you have to write the dates in this format or use the universal method: (yyyy/mm/dd) which converts the date to your local date setting whatever its format.

     

    Don't use the USA format, because your local date setting isn't based on that format.

    When you write 02/11/2017 and your local OS date setting is: (dd/mm/yyyy), that means 02/Nov/2017.

     

    • yshanelin's avatar
      yshanelin
      Copper Contributor
      Jun 15, 2023
      Thanks so much for the answer been hitting a wall re this issue. You just solved the issue I've been having!
    • Barbara Floyd's avatar
      Barbara Floyd
      Copper Contributor
      Nov 07, 2017
      Thanks Haytham
      Is there a cell format that I can use which will just accept whatever I input and not try to recognise it as a date? Eg it was formatted as general, but excel still tries to convert it to a date. What if I use ‘text’ format?
      The problem is I have to input it in USA format (despite being in the UK) because the system I am getting the date from is in USA format
      Thanks!
      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Nov 07, 2017

        Hi,

         

        My recommendations for you:

        #1 Don't store the dates in text format!

        #2 Continue to enter the dates according to your local format: (dd/mm/yyyy).

         

        Please note that the Excel workbook that contains that dates, when you send it to someone in the US, the dates won't remain in your local format: (dd/mm/yyyy), It will change to the US format (M/d/yyyy), and vice versa.

        The formatting changes, but the value is the same.

         

         

        To make it less confusing, follow these steps to unify the formatting:

        Step 1

        Open Control Panel

         

        Step 2

        Click Region and Language

         

        Step 3

        Select English (United States) Format, and make sure that the date format is: (M/d/yyyy), then click OK.

         

         

         

        After that, you'll notice that the dates format you already have will automatically change to US format.

        And you can now use the US date format: (M/d/yyyy) to enter the dates in your workbooks.

Resources