Excel Forum

CHANGE DATE FORMAT FROM USA TO UK

David Hutchinson

Member

Members

Forum Posts: 17

Member Since:
July 20, 2016

Offline

October 2, 2020 - 4:12 am

Good Day

Please will you assist me to be able to change the date from USA format to UK format. Secondly Pivot tables only can read some of the dates in column P. When changing the dates to a number some dates change but others do not. The Excel attachment is downloaded from an e-learning website – still in the format it was downloaded but some info has been removed and an index column has been added. (Other document download options from the website are Word or PDF.)

I have tried to change the dates using:

Cell Formatting to change the dates to UK format
Use Find & Replace to check if there are spaces in front of the dates
Text to columns using the date function
Copy a blank cell and use Paste Special and selecting And
Power Query using the Locale to see if the dates can be changed (I have Basic knowledge of Power Query)

I am using MS office 365 on a PC. My PC date setting is dd/mm/yyyy

Thanks for your help

Dave

See Answer

Purfleet

England

Member

Members

Trusted Members

Forum Posts: 415

Member Since:
December 20, 2019

Offline

October 2, 2020 - 6:12 am

When working with US dates i normally change my date formatting to Long Date, so i can eaisly see if the formula has worked.

Try =IFERROR(DATEVALUE(MID(P9,4,2)& "/" &LEFT(P9,2)& "/" &RIGHT(P9,4)),"")

Philip Treacy

Admin

Forum Posts: 1619

Member Since:
October 5, 2010

Offline

October 2, 2020 - 9:24 am

Hi Dave,

If you're getting this data from the web then you should try using Power Query to get it. But with the data supplied already in Excel, the transformations are the same.

I've converted the data into a table and then used PQ to Change Type Using Locale

#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Completed Date", type date}}, "en-US")

Please see attached file for working example.

Regards

Phil