Answered

Replace Excel Date Number in a Select Map using If expression

(0) Share

Report

Posted on by Riskyplan

I list rows from an Excel Spreadsheet that contain two Columns, one is a title the other a value. One of the rows contains a date. The date arrives as a number from 1/1/1900 that has to be composed into a date. This date is stored in a variable. I would like to run a select that maps Colum1 to Loan, and Colum2 to Value. Unless colum2 contains the the Excel formatted number. In this case I like to use the date variable that was created earlier in the flow. Below is the formula I am using, however the output for the if expression is "". What is wrong with my expression?

If(equals(outputs('List_rows_present_in_a_table_2')?['body/value'][0]?['Column1'],'Last Payment Date'),outputs('Compose_Last_payment_date'),outputs('List_rows_present_in_a_table_2')?['body/value'][0]['Column2'])

This is the out put when the flow runs.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

grantjenkins 11,063 Moderator on at

Like (0)

Report

When you retrieve your Excel data you can set DateTime Format to ISO 8601 so your dates come back as normal dates (not serial dates) and won't need to be converted.

Was this reply helpful? Yes No
Riskyplan 39 on at

Like (0)

Report

Thanks for the reply and solution that I was unaware of. Is there anything preventing the date from pulling in as ISO 8601. I still seem to be only pulling in the serial number format.

Was this reply helpful? Yes No
Riskyplan 39 on at

Like (0)

Report

Since I am using a named formula to create the date in the excel table I do not believe ISO 8601 setting will covert the serial number. I still am getting the serial number under list rows present in a table.

The original questions stands. basically how can I create an HTML table that has the excel table data organized in two columns replacing the serial date with a an actual date?

Was this reply helpful? Yes No
Verified answer

Riskyplan 39 on at

Like (0)

Report

Found the solution: Changed the formula used in the Select step to:
If(equals(Item()?['Column1'],'Last Payment Date'),outputs('Compose_Last_payment_date'),item()?['Column2'])

Was this reply helpful? Yes No