Forum Discussion

BCCTommyS's avatar
BCCTommyS
Copper Contributor
Sep 13, 2022

Convert Column from Date to Text with Date Format only IF Cell contains "something"

Hello everyone,

If anyone knows how to do this I'd be very grateful.

Since I am using Power Automate and it doesn't read values from cells/rows/columns in Date format I am manually converting a column of Date values into text with the format of my choosing using: =TEXT([@[Reference Date]], "MM/DD/YYYY") . and then using this new column, in this case: 'Reference Date Formatted' for my Power Automate Flow. 

While this is working fine, I am running into an issue where if there is no value in the referenced cell it will populate the cell of the new column with 01/01/1900. I understand why this occurs, but I'd like to avoid it.

Because of my programming background I am thinking of executing my formula of =TEXT([@[Reference Date]], "MM/DD/YYYY") only IF there is SOMETHING in the previously referenced column.

Any ideas how I might do this? 
Any suggestions / tips / formulas would be greatly appreciated.

Same thing where there is no value in referenced columns for going from currency format to text then back to currency using:=TEXT([@Discounts],"$#,##0.00")

 


I'd prefer a blank spaces in what is returned from my excel table 😊

 



  • BCCTommyS 

    I'd do that on Power Automate site, something like

    =formatDateTime(addDays('1900-01-01T00:00:00Z', int(item()?['MyDate'])), 'yyyy-MM-dd')

Resources