Convert Excel Dates and Currency to Text Without Blank Cells Showing as Zero
Question details
Convert date and currency values to text for Power Automate while returning an empty result for blank source cells.

- Product
- Microsoft Excel and Power Automate
- Device & OS
- not provided
- Scenario
- An Excel table supplies date and currency values to a Power Automate flow.
- Observed behavior
- Applying TEXT directly to a blank date or currency cell can produce a formatted zero instead of a blank result.
Identify the date and currency source columns in your Excel table and add separate columns for the text results. Confirm that populated date and currency cells contain values Excel recognizes as dates and numbers.
Check for Blank Cells Before Applying Excel TEXT
Use IF with TEXT to format populated values and return an empty string for blank source cells.
In a new Excel table column, enter =IF([@[Reference Date]]="","",TEXT([@[Reference Date]],"MM/DD/YYYY")) and replace Reference Date with your date column name.
In another table column, enter =IF([@Discounts]="","",TEXT([@Discounts],"$#,##0.00")) and replace Discounts with your currency column name.
Inspect rows with populated and empty source cells. Populated values should appear as formatted text, while empty source cells should produce empty results.
Save the workbook, then select the new columns from the Excel action's dynamic content in Power Automate.

Prepare Date and Currency Text in WPS Spreadsheets
Use IF and TEXT formulas in WPS Spreadsheets to create formatted text columns while leaving results empty when the source cells are blank.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
- 2. Create a date text column: In an empty column, enter =IF(A2="","",TEXT(A2,"MM/DD/YYYY")), replacing A2 with the date source cell.
- 3. Create a currency text column: In another empty column, enter =IF(B2="","",TEXT(B2,"$#,##0.00")), replacing B2 with the currency source cell.
- 4. Fill and save: Drag each formula's fill handle down the relevant rows, check the results, and use File > Save As to save the workbook as XLSX.

Frequently Asked Questions
Why does a blank date become a date when I use TEXT?
TEXT can interpret an empty cell as zero and format that value as a date. Wrap TEXT in IF, such as =IF(A2="","",TEXT(A2,"MM/DD/YYYY")), to return an empty result instead.
Will the formula hide an actual zero currency value?
No. A numeric zero is not an empty cell, so =IF(B2="","",TEXT(B2,"$#,##0.00")) formats it as $0.00.
How do I change the date format sent to Power Automate?
Change the format argument in TEXT. For example, use =IF(A2="","",TEXT(A2,"yyyy-mm-dd")) to produce a date such as 2026-06-30.
Is an empty formula result the same as a blank cell?
No. IF returns an empty string when the source is blank, but the formula remains in the cell. In Power Automate, check whether a later action requires an empty string or a null value.




