Export to Excel Sharepoint 2013 date and number (in lookup column) format issue to text not date

Question

Hoping someone can help as I am struggling to understand when Sharepoint exports to excel why is my lookup value columns exporting as text (should be date value), plus the formatting is different. Also having similar issue with a number field and it bringing over multiple decimal places after the number as 0.

I have validated my sharepoint is English US and my windows is the same. I can't edit the column that has the lookup value to be date format in my list view.

What I need is just US Date format 1/3/2021, column to be date format when I export, 2nd Number column to read just as 123 (not 123.000)

-1st image is of the list view with the lookup date (displays correct, and number) -2nd image is of the export to excel -Last image is the list view of the data source exported to excel with correct formatting

This has me completely stopped on a department project-frustrating as I have about 15-20 columns I have to do this on and add to other lists/views.

Answer 1

Hi @fourj_4 ,

I could reproduce your issue if the column type is lookup column with the correct regional format.

As a workaround, we can import this table into Excel Power Query, then transform the date format.

Detailed steps for your reference(My office version is 2016):

1. Select full table, then click From table under Data option:

2.Choose the date column, click Date Only under transform tab:

3.Then close and load the query to the worksheet.

]

Result for your reference:

---

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.