Menu
I have imported a CSV file with 2 long columns of dates. These dates are in the US format Aug/28/2013 and I want them to be in the standard UK dd/mm/yyyy format.
I have tried formatting the cells as US dates and then converting them to number-only formats, and various other permutations within the Date format box, but with no success.
Can anyone rid me of these awful US dates please?
912
February 13, 2019 by Paulie 5 Comments. If you have a column of dates in Excel that are in US format, it is easy to convert them to UK format. Simply follow these steps: Select the column containing the US Dates: From the Data Ribbon, Choose “Text to Columns”: Choose “Delimited” from the Text to Columns Wizard, then click next.
Simply follow these steps: 1 Select the column containing the US Dates: 2 From the Data Ribbon, Choose “Text to Columns”: 3 Choose “Delimited” from the Text to Columns Wizard, then click next. 4 Untick all the delimiters. 5 In the column data format area, choose “Date” and select “MYD” from the drop down menu: More items...
(For a US date in cell A2 and PC date is dd/mm/yy). If ISNUMBER is true, the US date looks like a UK date and Excel has serialized it as a number. So can format the date as text and back to a date again.
Because windows show the dates format by default according to your selected country region in the settings. Let’s see how to change. 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.
Another solution without using a formula:
The dates should now be converted to UK dates.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
