View Full Version : Date/Time import issue

2016-10-17, 09:45 PM
Good afternoon, I am a long time power user of Excel and have just broken into the Power everything realm.
I've read every post that I can find regarding date/time in Power Query (PQ) (by Ken and others) and tried everything I can think of to get this to work.
If I have missed something, please point me in the right direction.

I am attempting to import an XLS file downloaded from the client's website (legacy system generates a daily output file). In PQ, I delete the first 8 rows as it is primarily text and the column headers are scattered throughout the rows.

Column1 is a IndexId, Column2 is SubmitDate. In the actual xls file, the data in column 2 is formatted as yyyy-mm-dd, yet when I look at the data in column 2 in the formula bar, I can clearly see the data as mm/dd/yyyy hh:mm:ss am (or pm, as the case may be) and I need the data to be read into PQ in this format or as mm/dd/yyyy hh:mm:ss. Currently, PQ imports all the columns as Text. When I change column2 to be Date/Time, it reads the data as mm/dd/yyyy 12:00:00 am. Even if I try using "Use Local" and select English(US), I still get the data formatted as mm/dd/yyyy 12:00:00 am.

If I open the xls file using Excel, format column 2 as mm/dd/yyyy hh:mm:ss, close/save the file, and then import into PQ it works fine.

Is this one of those occasions where I need to use VBA to prep the files before bringing into PQ?

Any assistance would be greatly appreciated.


2016-10-17, 10:03 PM
I forgot to mention that I am running Office 365 Pro Plus and Excel version 16.0.6741.2071 and all updates are current.

2016-10-18, 03:26 PM

I'm sorry, I've read the post a few times and I can't figure out what date format you want as a result??

In Power Query you use "Using Locale..." to let PQ the source region

The format PQ then shows depends on the locale settings of your PC.

If your issue is that instead of getting a 12h hour time (AM/PM) you want 24h you should change your locale settings so that the short and long times use UPPER CASE H (HH:mm and HH:mm:ss)

2016-10-18, 03:51 PM
Thank you for the reply. I will try updating the settings on my PC and see if that takes care of the problem. The final format I need is mm/dd/yyyy hh:mm:ss (or may try HH:mm:ss as needed).