How to Convert Power Automate Date Text into Real Excel Dates
Question details
The user needs to convert date and time text generated by Power Automate into recognizable date values in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Exporting data from Power Automate to an Excel spreadsheet where dates are incorrectly formatted as text strings.
- Observed behavior
- Excel treats the imported Power Automate date and time as a text string instead of a real date, requiring manual re-entry to trigger the conversion.
Ensure that the date text from Power Automate is formatted consistently, preferably in ISO 8601 format (yyyy-MM-ddTHH:mm:ss), to prevent calculation errors during conversion in Excel.
Use the SUBSTITUTE Function to Convert ISO 8601 Dates
This is the most efficient method if Power Automate sends ISO 8601 formatted text containing a 'T' between the date and time.
When Power Automate exports an ISO 8601 date, it includes a 'T' character separating the date and time. Excel cannot natively recognize this string as a date until the 'T' is removed and the string is converted into a numeric value.
Ensure your Power Automate flow writes the date value in the ISO 8601 format, such as yyyy-MM-ddTHH:mm:ss.
In an adjacent blank Excel cell, type the formula =--SUBSTITUTE(A2,"T"," "), assuming A2 contains your text date. The SUBSTITUTE function replaces the 'T' with a space.
Select the cell containing the new formula result, right-click and select 'Format Cells'. Choose your preferred Date and Time format to display the numeric value correctly.
Combine DATEVALUE and TIMEVALUE for Complex Date Strings
Use this alternative method if you need to separate the date and time portions before converting them into valid Excel values.
Effortlessly Convert Text to Dates in WPS Spreadsheet
WPS Spreadsheet provides robust data processing capabilities, including full support for text-to-date conversion formulas, allowing you to easily handle imported data from Power Automate.
- 1. Open your exported data: Launch WPS Spreadsheet and open the file exported from Power Automate.
- 2. Input the conversion formula: Select the blank cell next to your date text and type =--SUBSTITUTE(A2, "T", " ").
- 3. Calculate and format: Press Enter to convert the text to a numeric value. Then, navigate to the Home tab, click the Number Format dropdown, and choose 'More Number Formats' to apply your preferred Date layout.
Frequently Asked Questions
Why does Excel treat my imported Power Automate dates as text?
When exporting data from external systems like Power Automate, the information is often transferred as plain text strings to preserve exact formatting. Excel does not automatically recognize these text strings as internal date serial numbers until they are explicitly converted using formulas or data tools.
Can I use the 'Text to Columns' feature to fix text dates?
Yes, you can select the column containing the date text, go to the Data tab, and click 'Text to Columns'. Proceed to step 3 of the wizard, select 'Date', and choose the corresponding format (e.g., YMD) to convert the text into real dates without writing formulas.
How can I format dates directly in Power Automate before sending them to Excel?
You can use the formatDateTime() expression in Power Automate. By utilizing an expression like formatDateTime(triggerBody()?['Date'], 'yyyy-MM-dd'), you ensure the date is exported in a clean, standardized format that Excel can more easily parse upon import.
