question

fourj4-6242 avatar image
0 Votes"
fourj4-6242 asked JoyZ commented

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

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.

90365-date-formating-issue-1.jpg
90393-exported-sharepoint-from-other-list-format-issue.jpg
90394-date-looking-up-1.jpg
90387-number-looking-up-from-1.jpg
90377-source-data-export-from-sharepoint-fine.jpg


90388-system-date.jpg


office-sharepoint-server-customization
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi fourj_4,

The [openspec-*] tags are dedicated to supporting issues related to open specifications. You can find open specifications here https://docs.microsoft.com/en-us/openspecs/

Since your question does not pertain to one of these specifications, I've removed the openspecs tag and added [office-sharepoint-onlin]. Hopefully this will get your post the proper attention.

If I've misunderstood your question and you believe this is related to the Open Specifications documents, please clarify, providing the document name and section with which you have the question or issue.

Best regards,
Tom Jebo
Sr Escalation Engineer
Microsoft Open Specifications



0 Votes 0 ·

They sent me to this linked site

0 Votes 0 ·

If you are implementing against one of the specifications here:
https://docs.microsoft.com/en-us/openspecs/

Then please provide the specification you are implementing and the code you use to parse. Otherwise, this would be a Sharepoint product issue and the [office-sharepoint-*] tags would be more appropriate.

Tom

0 Votes 0 ·

1 Answer

JoyZ avatar image
0 Votes"
JoyZ answered JoyZ commented

Hi @fourj4-6242,

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

90752-image.png

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:

90709-image.png
2.Choose the date column, click Date Only under transform tab:

90753-image.png
3.Then close and load the query to the worksheet.

![90771-image.png]

Result for your reference:

90689-image.png


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.



image.png (9.9 KiB)
image.png (37.0 KiB)
image.png (48.8 KiB)
image.png (21.8 KiB)
image.png (10.5 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JulieWang-MSFT,

Thank you for this reply...Yes this works as a work around, but I would have to teach some 100+ employees how to do these extra steps plus teach power query which most know basic excel. I don't think I will get buy in to new process if this has to be done every time they export a list daily. I have been asked to find the fix now or abandon the project by next week.

In power query all the data appears as it should before I began editing...I have about 15-20 columns that would need the change done to each time, each user.

Thank you so much for your time and help!

0 Votes 0 ·

Hi @fourj4-6242,

In order to get a better forum experience, we changed your answer to a comment.

I think this is by design in SharePoint.

If you find any answer helpful to you, please remember to accept it as answer.

Thank you for your understanding.

0 Votes 0 ·

Hi @fourj4-6242,

If you find any replies helpful to you, please remember to accept them as answers.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

0 Votes 0 ·