cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Resident Rockstar
Resident Rockstar

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.

View solution in original post

6 REPLIES 6
Resident Rockstar
Resident Rockstar

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.

View solution in original post

Solution Supplier
Solution Supplier

@djpejsen please use the expression below:

 

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

Regular Visitor

 

My current flow

 

batch expiration flow.jpg

 

Resident Rockstar
Resident Rockstar

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

dateDiff.png

Regular Visitor

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'))
Regular Visitor

djpejsen_0-1606539832999.png

 

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (7,048)