How to Import Excel Date and Time Values with Seconds into SharePoint
Question details
The user needs a method to import date and time data from an Excel spreadsheet into a SharePoint list while preserving the exact precision of seconds.
- Product
- Microsoft SharePoint, Microsoft Excel, Power Automate
- Device & OS
- not provided
- Scenario
- Importing spreadsheet data containing precise timestamps (including seconds) into a SharePoint list for accurate record-keeping.
- Observed behavior
- SharePoint Date and Time columns reject or truncate imported Excel values that include seconds because the standard SharePoint column format does not natively support that level of precision.
Ensure you have access to Microsoft Power Automate and sufficient site permissions to create and modify columns in your target SharePoint list. Verify that your source Excel data is formatted correctly and stored in a Table format so Power Automate can read it.
Import Precise Date and Time Using Power Automate and a Text Column
Bypass SharePoint's default Date and Time column limitations by storing the precise timestamp in a single-line text column using a Power Automate workflow.
SharePoint natively drops seconds in its standard Date and Time columns. To preserve this data when importing from Excel, the most reliable workaround is converting the timestamp into a formatted text string during the automated import process.
Navigate to your SharePoint list, click on '+ Add column' at the top of the list, select 'Single line of text', and name it to represent your precise date/time field (e.g., 'Exact Timestamp'). Click 'Save'.
Log into Power Automate, click 'Create' on the left menu, and choose 'Instant cloud flow' or 'Automated cloud flow'. Add the Excel Online 'List rows present in a table' action and point it to your source Excel file.
Click 'New step' and search for the SharePoint 'Create item' action. Select your Site Address and List Name. The fields for your SharePoint list will appear, including the new text column you created.
Click inside the input box for your new text column. In the dynamic content pop-up panel, click the 'Expression' tab and enter: formatDateTime(outputs('Create_item')?['body/Date'],'MM/dd/yyyy HH:mm:ss tt'). Replace 'body/Date' with the exact reference name of your Excel date column, then click 'OK'.
Click 'Save' at the top right, then click 'Test' to run the flow manually. Check your SharePoint list to confirm that the precise date and time, including the seconds, have populated accurately in the text column.
Manage Your Spreadsheet Data Seamlessly with WPS Office
If you frequently manage complex datasets and need a reliable, lightweight alternative to Microsoft Office, WPS Office offers comprehensive spreadsheet tools. It allows you to format, clean, and prepare your timestamp data before exporting to web databases, completely free of hefty subscription fees.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free suite for your operating system, and follow the standard installation prompts.
- 2. Open Your Data File: Launch WPS Spreadsheet and open your existing .xlsx or .csv file containing the timestamp data you wish to prepare.
- 3. Format Cells for Precision: Select your date columns, right-click and choose 'Format Cells'. Under the Custom category, apply a format like 'mm/dd/yyyy hh:mm:ss' to verify your data before export.
Frequently Asked Questions
Why does SharePoint natively drop seconds from Date and Time columns?
SharePoint's default Date and Time column type is optimized for standard business tracking (which usually only requires minute-level precision). It does not natively store or display the seconds without converting the data to text or using customized JSON column formatting.
Can I calculate time differences if I store the date as text in SharePoint?
If the date and time are stored in a Single-line text column, SharePoint cannot natively perform chronological calculations (like calculating days between two dates) in standard calculated columns. You will need to perform these calculations in Power Automate or your source spreadsheet software before importing.
How do I ensure Power Automate reads the correct date format from Excel?
When using the 'List rows present in a table' action in Power Automate, click on 'Show advanced options' and set the 'DateTime Format' to 'ISO 8601'. This prevents Power Automate from reading your precise dates as raw Excel serial numbers.
Is it possible to use calculated columns in SharePoint to extract seconds?
No. Because SharePoint's native Date/Time field discards the seconds immediately upon saving the record from a standard input, a calculated column referencing that date field has no seconds data to retrieve. Extracting the time to a text field via Power Automate remains the most reliable method.
