Function Problems

Convert Excel Dates and Currency to Text Without Blank Cells Showing as Zero

Amos GikundaAmos Gikunda Nov 18, 2026 868 views

Question details

Convert date and currency values to text for Power Automate while returning an empty result for blank source cells.

Convert Excel Dates and Currency to Text Without Showing Blanks as Zero
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a date text formula

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.

2
Add a currency text formula

In another table column, enter =IF([@Discounts]="","",TEXT([@Discounts],"$#,##0.00")) and replace Discounts with your currency column name.

3
Check the results

Inspect rows with populated and empty source cells. Populated values should appear as formatted text, while empty source cells should produce empty results.

4
Use the text columns in your flow

Save the workbook, then select the new columns from the Excel action's dynamic content in Power Automate.

Check for Blank Cells Before Applying Excel TEXT
Empty results are formulas: The formulas return an empty string rather than a truly empty cell. Check how your flow handles empty strings if a later action requires a null value.
Format spreadsheet values in WPS Office

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
  2. 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. 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. 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.
Convert dates and currency to text without displaying blanks as zero.Open and save Microsoft Excel XLSX files.Use familiar spreadsheet formulas and cell references.
microsoft office alternative - wps office

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.