How can I tell SharePoint "Export to Excel" to store a folder name as text rather than translate to a number in date format?

Question

How can I tell SharePoint "Export to Excel" to store a folder name as text rather than translate to a number in date format?

JP 20

When exporting a list of files from SharePoint to Excel using the in-built function "Export to Excel", a folder named 2023-05-23 is stored numerically as a date (23/05/2023) in the Name field instead of text "2023-05-23" (as preferred).
This means that the full path (Path field & "/" & Name field) sorts incorrectly in Excel.
I am looking for a fix to the export method or a way of returning the folder name to text using the same format used in the directory name (I have no control over the original folder names or the way they may display date).

p.s. I cannot set up a manual datalink to SharePoint as the site seems to be secured, and editing the line in the .iqy "DisableDateRecognition=False" to "True" does not have the desired effect.

Emily Du-MSFT 51,946 Reputation points Microsoft External Staff

2023-10-02T01:33:15.0666667+00:00

Thank you for your question.

We are currently looking into this issue and will give you an update as soon as possible.

Answer accepted by question author

0 additional answers

Your answer

Emily Du-MSFT 51,946 Reputation points Microsoft External Staff

2023-10-02T01:33:15.0666667+00:00

Thank you for your question.

We are currently looking into this issue and will give you an update as soon as possible.

Answer 1

Emily Du-MSFT 51,946 Microsoft External Staff

The "Export to Excel" function is integrated by SharePoint, there is on option to change it.

As a workaround, we can use formula to format data in the excel file.

1.Click Name column -> Format Cells -> Date -> Choose "YYYY-MM-DD".

2.In the Full path column, use this formula: =E2&"/"&TEXT(A2,"YYYY-MM-DD").

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

JP 20 Reputation points

2023-10-02T07:29:01.39+00:00

Thank you for the response. Part of the problem as indicated is the "date format" used in the folder name is not controlled by me, so the formula will not work if the creator uses a different date format, for example YY-MM-DD or DD-MM-YYYY.
p.s. I have found another workaround using VBA to convert the hyperlink address provided by SharePoint to ASCII characters and abstracting the original filename, but I would prefer a method without resorting to macros.
Emily Du-MSFT 51,946 Reputation points Microsoft External Staff

2023-10-02T10:08:56.6333333+00:00

Yes, the date format used in the folder name is a problem.

And maybe flow is an alternative.
JP 20 Reputation points

2023-10-03T08:40:28.9466667+00:00

Hopefully Microsoft will fix the SharePoint Excel Export issue, but in the meantime Power Automate looks very useful once I can get more than an empty list [] from the Get files (properties only) function.
I'm currently checking whether authorization is required to get this to work.

Share via

How can I tell SharePoint "Export to Excel" to store a folder name as text rather than translate to a number in date format?

0 additional answers

Your answer