Add a date difference variable to each index in a Excel array (list rows present in table) and add to HTML Table

[djpejsen]

Hi 

I have created a flow which uses an Excel table with product batch numbers as input 
One of the columns in the Excel table is  "expiration date" 

I would like to calculate the number of days between UTCNow and Expiration Date for each index in the array (Apply to each) and add the value as an additional column to the HTML table. 

I know how to calculate the data difference by using ticks from UtcNow() and Expirations date, but I cannot figure out how to add the "dateDiff" to my output Excel array/ Input HTML 

[Paulie78 · Accepted solution]

Add a select action right after your Excel action. Use the excel data to populate the columns and create a new column with a name of your choosing and then put your expression to calculate the difference in the new column. 

You'll have to use item()['expiration date'] as the input variable in your expression.

Then use the output of your select to build your HTML table.

[krootz]

@djpejsen please use the expression below:

div(sub(ticks(utcNow('yyyy-MM-dd')),ticks(formatDateTime(<YourExpirationDateDynamicContent>,'yyyy-MM-dd')),864000000000))

[djpejsen]

My current flow

[Paulie78]

Hard to tell from the screenshot, but I think this is what you need to do:

[djpejsen]

the final solution

div(sub(ticks(utcNow('yyyy-MM-dd')),ticks(addDays('1899-12-30',int(item()?['Expiration date']),'yyyy-MM-dd'))),variables('864_billions'))

[djpejsen]