Md. Sourov Hossain Mithun
Excel offers the feature to change the date format country-wise. And the way is so simple in Excel if you know the tricks. So this article will provide you with 3 quick ways to change the default date format from US to the UK in Excel with sharp steps and clear illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
3 Ways to Change Default Date Format from US to UK Standard in Excel
To explore the methods, we’ll use the following dataset which represents 5 nominated movies in the best picture category for the Oscar award 2022. Have a look at the dates in US format. Let’s change it to a UK format using the following methods.
1. Change Regional Settings to Change Default Date Format from US to UK in Excel
One of the easiest ways is to change the Country Region from the windows settings. Because windows show the dates format by default according to your selected country region in the settings. Let’s see how to change.
Steps:
- Open Control Panel.
- Then click on Clock and Region.
A dialog box named Region will open up.
- Select English (United Kingdom) from the Format box.
- Finally, just press OK.
Now see that the dates are changed to UK format.
Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
2. Create a Custom Date Format to Change Default Date Format from US to UK in Excel
Another easy way is to create a custom date format from the format settings in Excel. We’ll change the region here too using the format settings.
Steps:
- Select the range of dates.
- Later, click the icon from the Number section of the Home tab as shown in the image.
Soon after, the Format Cells dialog box will open up and it will take you to the Date format settings directly.
- Select English (United Kingdom) from the Local (location) box.
- Finally, just press OK.
Then you will get that Excel has changed the date format exactly as you wanted.
Read More: How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
Similar Readings:
- Excel Formula for Current Month and Year (3 Examples)
- Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
- How to Get Last Day of Previous Month in Excel (3 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
3. Use Text to Columns Wizard to Change Date in Text Format to the UK Standard
If you store dates as text values and after that, if you use the above methods to change the format from the US to the UK then nothing will happen. You will have to change the text format to date format using the Text to Columns Wizard and then you will be able to change the date format. Have a look at the image below, the dates are stored as Text and as US format. And we know text values are aligned to the left in Excel.
Steps:
- Select the range of dates.
- Then click as follows: Data > Data Tools > Text to Columns Wizard.
Soon after you will get a dialog box with 3 steps.
- In the first step, mark the Delimited option.
- Then press Next.
- From the second step, unmark all options and press Next.
- In the final step, mark the Date.
- Then select MDY from the drop-down beside the Date option.
- Finally, just press Finish.
Now have a look that the Text format is changed to Date format as UK format.
Read More: How to Convert Text to Date with Excel VBA (5 Ways)
Conclusion
I hope the procedures described above will be good enough to change the default date format from the US to the UK in Excel. Feel free to ask any question in the comment section and please give me feedback.
How to change the date format of the timestamp in Excel comments. It’s in US mm/dd/yyyy format despite all my Windows, Office, SharePoint, browser, MS profile settings specifying English(Australia) which like almost all locales is dd/mm/yyyy.
Hi Mike,
You can try the following steps to change the date format in excel comments:
1) Type intl.cpl in the Windows Search Box.
2) Now, under Date and time formats, click on the Short date drop-down and choose the date format that you want to use in the comments in excel.