Fix Incorrect Date and Time Values Exporting SharePoint to Excel
Question details
Date and time values shift by several hours and display extra characters when exported from a SharePoint list to Excel via a Power Automate flow.
- Product
- Microsoft SharePoint / Excel / Power Automate
- Device & OS
- not provided
- Scenario
- Exporting list data automatically from SharePoint to an Excel worksheet using Power Automate.
- Observed behavior
- The exported date and time values shift by a specific amount of hours (e.g., seven hours) and contain unwanted extra characters due to unhandled serialization and UTC conversion.
Before modifying your Power Automate flow, identify the default regional time zone of your SharePoint site and the local time zone where the final Excel file will be analyzed.
Adjust Time Zone and Format Using Power Automate Expressions
Use the convertTimeZone() and formatDateTime() functions in Power Automate to ensure dates export to Excel correctly without shifting or retaining serialization characters.
SharePoint natively stores date and time values in Coordinated Universal Time (UTC). When a Power Automate flow writes this raw data to Excel, it often retains the UTC format or serializes it (adding 'T' and 'Z' characters), causing a time shift when viewed in your local time zone.
Navigate to Power Automate, open your existing flow in Edit mode, and locate the step where you insert SharePoint list data into the Excel file.
Click on the input field for the Excel date column. Instead of inserting the dynamic SharePoint date directly, click on the 'Expression' tab in the dynamic content menu.
Enter the time zone conversion function specifying your source and destination time zones. For example: convertTimeZone(item()?['DateColumn'], 'UTC', 'Pacific Standard Time').
To remove the extra 'T' and 'Z' characters, wrap the conversion inside a formatDateTime() function. Your final expression should look like: formatDateTime(convertTimeZone(item()?['DateColumn'], 'UTC', 'Pacific Standard Time'), 'yyyy-MM-dd HH:mm').
Click 'OK' to apply the expression, save your flow, and run a test to verify the Excel output displays the correct local time.
Verify and Align SharePoint Regional Time Zone Settings
Ensure the SharePoint site's regional settings are configured to your local time zone to rule out baseline configuration discrepancies.
Seamlessly Open Exported Excel Files with WPS Office
If you are dealing with complex Excel data exports from SharePoint or other automated platforms, WPS Office offers a highly compatible, lightweight, and completely free alternative to Microsoft Office. Enjoy a familiar interface to easily view, format, and edit your exported .xlsx files.
- 1. Download and Install: Download the free WPS Office suite from the official website and follow the simple installation prompts.
- 2. Open Your Exported Data: Launch WPS Spreadsheet and securely open the .xlsx files generated by your SharePoint and Power Automate flows.
- 3. Apply Custom Formats: Highlight any date columns, right-click to select 'Format Cells', and instantly apply your preferred local date and time format.
Frequently Asked Questions
Why does SharePoint export dates exactly 7 hours behind my local time?
SharePoint securely stores all date and time fields internally in UTC (Coordinated Universal Time). If your local time zone is UTC-7 (like Pacific Daylight Time) and your export process simply dumps the raw data, the dates will appear 7 hours behind when opened in Excel.
How do I remove the 'T' and 'Z' characters from my exported Excel dates?
The 'T' and 'Z' characters indicate the ISO 8601 serialized date format (e.g., 2023-10-01T12:00:00Z). You can remove them in Power Automate by wrapping the dynamic content in the formatDateTime() expression, specifying a clean format string like 'yyyy-MM-dd HH:mm'.
Can I fix the shifted dates directly in Excel after the export is finished?
Yes. You can fix shifted dates in Excel without changing the automated flow by adding or subtracting the time difference mathematically. For example, adding +(7/24) to the cell value will shift the time forward by 7 hours. You can then apply a custom Date/Time cell format to hide any decimals.
