none
Reading Excel Cell Contents vs Formatting RRS feed

  • Question

  • Hi guys

    I have a script that is consuming ~500 excel files.  Most files contain a date record formatted as "dd-mm-yyyy" whereas a few files are "dd-mmm" (e.g. 12-Jun).  Unfortunately PQ is defaulting the latter to the year 2016 because it's reading the cell formatting as opposed to the actual serial date code stored in the cell.

    My question: how do to tell PQ to read a cells contents (e.g. a date of 42535) as opposed to its formatting (e.g. a date of 6/14/2016)?

    Thanks,
    Simon

    Tuesday, August 23, 2016 3:05 PM

Answers

  • Simon, although Excel stores a date as a serial number internally, the formula bar shows the full date in the regional format, regardless of the cell formatting. So if the regional date format is US, and if the formula bar displays 6/14/2015, and the cell format is 14-Jun, Power Query should display the date as 6/14/2015, not 6/14/2016.

    On the other hand, if someone enters 14-Jun in a cell, the date in the formula bar will default to the current year. So in one of the files that has the date formatted without the year, click one of the date cells and check the year in the formula bar. If the year differs from the year imported into PQ, then you have a problem that I cannot duplicate.

    • Marked as answer by Simon Nuss Thursday, August 25, 2016 12:28 PM
    Wednesday, August 24, 2016 6:00 PM

All replies

  • Simon, although Excel stores a date as a serial number internally, the formula bar shows the full date in the regional format, regardless of the cell formatting. So if the regional date format is US, and if the formula bar displays 6/14/2015, and the cell format is 14-Jun, Power Query should display the date as 6/14/2015, not 6/14/2016.

    On the other hand, if someone enters 14-Jun in a cell, the date in the formula bar will default to the current year. So in one of the files that has the date formatted without the year, click one of the date cells and check the year in the formula bar. If the year differs from the year imported into PQ, then you have a problem that I cannot duplicate.

    • Marked as answer by Simon Nuss Thursday, August 25, 2016 12:28 PM
    Wednesday, August 24, 2016 6:00 PM
  • I found the issue - the 500 Excel files are just old. They're *.xls and quite buggy. If I open them up in Excel 2013 and save them again then the dates are consumed correctly after re-running the script.


    I wish I could provide an example of the files but I can't due to client confidentiality. I've even tried obfuscating the data but as soon as I save the file the problem goes away.

    Thursday, August 25, 2016 12:28 PM