Date format of exported receipt

[K]

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.

[Andy]

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. 

[K]

thanks. i'll try it.

 

[K]

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

 

[K]

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. 

 

[Andy]

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.

[K]

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. 

 

 

[Georg]

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

[K]

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.