K Posted September 3, 2020 Share Posted September 3, 2020 Hi, I tried to use excel pivot table with exported receipt data. But, the date format of the exported data is not consistent. I exported 8/31/2020 and 9/1/2020. Then the date of the 8/31/2020 shows as a text format such as 31/08/2020 09:00 but 9/1/2020 shows as 1/9/2020 09:00 and it shows as date format. So If I tried to converse the date/time to date format I should converse separately. How can I fix this problem? Thanks in advance. I attached screen shot. Link to comment Share on other sites More sharing options...
Solution Andy Posted September 7, 2020 Solution Share Posted September 7, 2020 Hello. The format of the cells depends on your Excel settings. We recommend opening CSV files vie Google Sheets. Some of our users also apply CSV to XLSX (EXCEL) Converter before opening the CSV file in MS Excel. Link to comment Share on other sites More sharing options...
K Posted September 7, 2020 Author Share Posted September 7, 2020 thanks. i'll try it. Link to comment Share on other sites More sharing options...
K Posted September 7, 2020 Author Share Posted September 7, 2020 Hi, Andy! I did it with google sheets. But the result is same. As you can see it at the attached screen shot, 9/1/20 is shown as a date format and 8/31/20 is shown as a text format. Is there anything I missed? Thanks Link to comment Share on other sites More sharing options...
K Posted September 7, 2020 Author Share Posted September 7, 2020 I tried CSV to XLSX (EXCEL) Converter as recommended with the same result. As you can see, I transposed the date/time into date with the formula. 9/1/20 field was transposed as expected. 8/31/20 was not. all the date of August showed same result. Link to comment Share on other sites More sharing options...
Andy Posted September 8, 2020 Share Posted September 8, 2020 While opening CSV in google sheets, pay attention to keeping text format for all cells: In the Import file settings window, select ‘No’ for ‘Convert text numbers and dates’, and then click on the ‘Import data’ button. Link to comment Share on other sites More sharing options...
K Posted September 8, 2020 Author Share Posted September 8, 2020 Thank you for your response. But the result is same. I need to transpose date/time to date. Whatever formula I used the result is same. September one can be transposed but the August one result in error. Link to comment Share on other sites More sharing options...
Georg Posted September 10, 2020 Share Posted September 10, 2020 Hello Let me clarify a question. 1.Did you export this receipts report, right? https://prnt.sc/uf4pay 2.Would you like to see the dates of the receipts as on this screenshot? https://prnt.sc/uf4oy8 Link to comment Share on other sites More sharing options...
K Posted September 10, 2020 Author Share Posted September 10, 2020 Yes. I exported the data for the 8/31/20 thru 9/5/20 and I found out the date format of 8/31/20 and September ones are not identical. So when I try to transpose the date/time into date it case the error for 8/31/20. Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now