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
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)),"")
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
Hi David
The Data Text To Column method works for me (Excel 2010)
When selecting the date option make sure you selected MDY (actual format as per downloaded data i.e. US)
It should convert to your Windows date format (mine is set to English - United Kingdom)
Hope this helps.
Sunny
Thankyou very much for your help you have saved me a bunch of time 
You're welcome.
