Blog

How to Change American Date to UK Date in Excel

Do you want to change the date format in your Excel worksheet from American to British? Or do you want to make your dates consistent and compatible with your location and preferences? If so, you can use various methods to change american date to UK in Excel and convert the month-day-year format to day-month-year format. Changing american date to uk in Excel can help you avoid confusion and errors in your data and calculations. In this article, I will show you how to change american date to uk in Excel using the text to columns feature, the format cells dialog box, and the formula approach.

  • Change american date to uk using the text to columns feature: This is one of the easiest and quickest ways to change american date to uk in Excel. It involves splitting the date into three parts (month, day, and year) and then recombining them in a different order (day, month, and year). To do this, follow these steps:
    1. Select the column that contains the dates in American format.
    2. Go to the Data tab and click Text to Columns in the Data Tools group. This opens the Text to Columns Wizard.
    3. Select Delimited and click Next.
    4. Uncheck all the delimiters and click Next.
    5. Select Date and choose MDY from the drop-down list. This tells Excel that your dates are in month-day-year format.
    6. Click Finish. This will split your dates into three columns: month, day, and year.
    7. Select the three columns and copy them (Ctrl+C).
    8. Select an empty cell where you want to paste your dates in British format.
    9. Right-click and choose Paste Special. This opens the Paste Special dialog box.
    10. Select Values and click OK. This will paste your dates as values without any formatting.
    11. Select the column where you pasted your dates and go to the Home tab.
    12. Click Format Cells in the Number group. This opens the Format Cells dialog box.
    13. Select Date and choose a format that has day-month-year order. You can also create a custom format by typing dd/mm/yyyy in the Type box.
    14. Click OK. This will apply the British date format to your dates.
  • Change american date to uk using the format cells dialog box: This is another way to change american date to uk in Excel. It involves changing the regional settings for your worksheet and then applying a British date format to your dates. To do this, follow these steps:
    1. Select the column that contains the dates in American format.
    2. Go to the File tab and click Options. This opens the Excel Options dialog box.
    3. Click Advanced on the left pane.
    4. Scroll down to the Display options for this worksheet section on the right pane.
    5. Select English (United Kingdom) from the Locale (location) drop-down list. This tells Excel that you want to use British regional settings for your worksheet.
    6. Click OK. This will close the Excel Options dialog box.
    7. Select the column that contains your dates and go to the Home tab.
    8. Click Format Cells in the Number group. This opens the Format Cells dialog box.
    9. Select Date and choose a format that has day-month-year order. You can also create a custom format by typing dd/mm/yyyy in the Type box.
    10. Click OK. This will apply the British date format to your dates.
  • Change american date to uk using a formula: This is a more advanced and flexible way to change american date to uk in Excel. It allows you to create a new column with dates in British format without changing or affecting your original data. To do this, follow these steps:
    1. Select an empty cell where you want to enter your formula.

      2. Here is the rest of the article:

    2. Type =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) and press Enter. This formula extracts the year, month, and day from the date in cell A2 (assuming your dates are in column A) and uses the DATE function to create a new date in British format.
    3. Copy the formula down to the other cells in the column. This will create a new column with dates in British format.
    4. Select the column that contains your formulas and copy them (Ctrl+C).
    5. Select an empty cell where you want to paste your dates as values.
    6. Right-click and choose Paste Special. This opens the Paste Special dialog box.
    7. Select Values and click OK. This will paste your dates as values without any formulas.
    8. Select the column where you pasted your dates and go to the Home tab.
    9. Click Format Cells in the Number group. This opens the Format Cells dialog box.
    10. Select Date and choose a format that has day-month-year order. You can also create a custom format by typing dd/mm/yyyy in the Type box.
    11. Click OK. This will apply the British date format to your dates.

In conclusion, changing american date to UK in Excel can help you change the date format in your worksheet from American to British and make your dates consistent and compatible with your location and preferences. You can use various methods to change american date to uk in Excel using the text to columns feature, the format cells dialog box, and the formula approach. By using these methods, you can avoid confusion and errors in your data and calculations.