Quick Books export to Excel: Date Format

Pumpkin_rrr on 25/10/2016 - 14:45

I have been using QB for a while and it seems ok so far. However, the biggest problem I have is the Date Format when exported to Excel/ CSV. I have tried chatting to them and email but to no avail. They admitted they have very limited Excel knowledge :-(

This is rather amazing given that the purpose to export is to enable us to prepare reports in our desired format. How is that possible when the dates are not even in Date Format?

I discovered that if I click on each cell one-by-one, it will switch to Date Format. But this is not practical!

Please help! Many thanks.

Financial

Comments

  • pernunz on 25/10/2016 - 15:06

    I'm not sure how QB exports it, but can't you select the whole column, then go into the cell properties (Ctrl + 1) and select the date format you are after?

    • Pumpkin_rrr on 25/10/2016 - 15:08

      Not sure what you meant there. But the cell is simply not a date format. The only way is to click on it one-by-one which will "trigger" a change in the format.
      Have you used QB before?
      I have pretty advanced Excel skills including Macro and Pivot etc…

  • tomsco on 25/10/2016 - 16:22

    I'm also interested to know. I've experienced this too in Excel, but not with Quickbooks. So I wonder if it's more a Excel problem than Quickbooks.

    • Pumpkin_rrr on 25/10/2016 - 17:00

      As I havent used QB for too long so need to make a disclaimer…. I thought when I first used it, the Export went well. However I think it was after a recent update that the format changed. Anyway, stand corrected.
      The most frustrating part is I have done all I could: emailed QB staffers with my downloaded file, chat, email…. and from what I gather, they really do not understand what's the fuss about the format :-(

      • tomsco on 25/10/2016 - 20:26

        I feel your pain.

        I am lucky that I've only had to do it with short reports, but if you have a lot of dates, it would be ridiculous.

        • Pumpkin_rrr on 25/10/2016 - 20:36

          Thanx. That actually makes me feel better now…

  • [Deactivated] on 25/10/2016 - 21:44
    +1

    Haven't seen a QB export, but from what you're describing it could be in text format and clicking each cell is converting to date. If that's the case you could create a new column and use DATEVALUE to refer to the column with dates in it

    Make sure the column with your datevalue formula is set to date format or all you'll see are the numbers Excel stores as dates eg 40685 is 22 May 2011, 40686 is 23 May 2011 and so forth (being that many days since 1 Jan 1900)

    https://support.office.com/en-us/article/DATEVALUE-function-…

    • Pumpkin_rrr on 27/10/2016 - 10:46

      Thanks so much for sharing Jay. Unfortunately the cell is not showing that way…

  • trex on 26/10/2016 - 14:47
    +1

    In the column where the current date field is, select the whole column and under the Data tab, choose Text to Columns. Don't split the column into any new columns and in the last step, choose Date as the Column data format.

    • Pumpkin_rrr on 27/10/2016 - 10:48

      Thanks a Million Trex! Cant believe it, it works!!! How do I thank you? Wonderful, it really made my day!
      Hope you have a great day too!

Login or Join to leave a comment
Login Join