thread Re: transaction exporting - date error in Archived Discussions (Read Only)

transaction exporting - date error

LDS — Tue, 24 Apr 2018 03:03:11 GMT

I am trying to export my transactions for 2017 (Jan 1, 2017 to Dec 31, 2017) and when I export it to excel (CVS) the transaction date format gets completly messed up - sometimes the year (ie 2017) becomes the day or month and sometimes the day becomes the month or year! Am I really supposed to go through 200 lines and revise the dates manually? I had the same issue last year and a Square support person helped me figure it out but I can't belive this is still an issue. Any suggestions?

ps. sent an email to support as well.

***solution found: I opened the spreadsheet with google docs sheets and it organized the dates properly***

Re: transaction exporting - date error

RHatch — Wed, 25 Apr 2018 12:54:38 GMT

This is just a simple format change. you can do this is any spreedsheet software. Each one is a bit different but the basics are select the column the dates are in and change the format to the date format you want. Yes Google sheets and Apple Numbers does a better job of recognizing the data as a date then excel.

Re: transaction exporting - date error

LDS — Wed, 25 Apr 2018 13:49:00 GMT

I wish it were that simple! Let me give you an example - I have a transaction that took place on Nov 10, 2017. After exporting it into excel, the date looks like 2011-10-17. Another one was processed on Feb 6, 2017 and looks like 2002-06-17. No mater how I format the date column, the years will still be 2011 or 2002 in these cases (unless there is some excel trick I`m not aware of). On the other hand some are exported and appear correctly. . I imported the excel doc into Google and it (somehow) corrected all the dates.

I just don`t understand how the date formatting can be so random when I export it.

Re: transaction exporting - date error

RHatch — Wed, 25 Apr 2018 13:59:09 GMT

There are some versions of Excel that had issues importing some data from .csv file. I use Google Sheets for all my stuff now.

Re: transaction exporting - date error

mcleroux — Mon, 11 Jan 2021 17:05:34 GMT

When I import my monthly item sales to excel for my reporting, the dates get all mixed up, even if I try to modify it with the excel formatting for dates, it just does not correct this error, so I must modify all of the dates individually, please tell me there is a way to fix this in excel as I do not use other software for my reporting.

Re: transaction exporting - date error

Valentina — Tue, 12 Jan 2021 01:28:50 GMT

It sounds like your Excel spreadsheet has changed to US formatting, but you are located in Canada @mcleroux - is that right?