Hello,
because of my flow i use ecxel online. During my flow i store somes dates in that excel sheet. First the date of receipt and second the date of evaluation. Now I want to have the duration (DAYS) between the two. Sadly i can not subtract the two dates like i could do with desktop ecxel version. Because if i subtract these two dates, the result is #value. Hope there are a solution.
Solved! Go to Solution.
Hi!
I just typed them in my first two Compose action blocks:
Hope this helps
Proud to be a Flownaut!
Hi!
Two questions:
1.-how do you currently store date value on the the excel columns? As 'Date' format, so you transform them into integer first? Or just regular text?
2.-I assume you need to read the excel row to get both column before substracting them, right?
If you can please perform a 'Get a row' or 'List rows present in a table' and share the outputs of a row with both Date column values filled, we can provide further assistance
Thanx!
Proud to be a Flownaut!
Hello,
1. I store the dates in that following formate
2. And it looks like that, if i perform get a row
So actually the same formate.... but i dont know
Hi!
Thanx for this superb feedback! So this means the Excel column format is not Date.
I just need some time to replicate a test scenario, so I can suggest further steps to be added after 'Get a row'. I guess the magic shall be based on both split() and ticks() WDL funtions
Thanx!
Proud to be a Flownaut!
Hi again!
So I stored temporarily both dates you obtain through 'Get a row' in two Compose action blocks:
Now I transform them into yyyy/MM/dd format, and finally a calculate the interval
You can compact everything in a single WDL expression, but I believe it is easier to understand this way
Interval is calcultated by using the following WDL expression:
div(sub(ticks(outputs('Compose_Frist_Beurteiler_Transformed')),ticks(outputs('Compose_Eingang_des_BVW-Vorschlags_Transformed'))),864000000000)
Intermediate steps (i.e. transform date format into yyyy-MM-dd is achieved by means of the following WDL expressions:
concat(split(outputs('Compose_Eingang_des_BVW-Vorschlags'),'.')[2],'/',split(outputs('Compose_Eingang_des_BVW-Vorschlags'),'.')[1],'/',split(outputs('Compose_Eingang_des_BVW-Vorschlags'),'.')[0])
concat(split(outputs('Compose_Frist_Beurteiler'),'.')[2],'/',split(outputs('Compose_Frist_Beurteiler'),'.')[1],'/',split(outputs('Compose_Frist_Beurteiler'),'.')[0])
Now, once I execute my test flow with the example dates you provided I obtain 14 as the final result
Hope this helps
Proud to be a Flownaut!
Thank you but i still have some problems. I did it like you showed.
But now there are some faults and it said, that please correct the actions Verfassen, Verfassen 1 and Verfassen 2 as there is no valid reference. Maybe you have an idea to solve that.
okay i corrected verfassen 3 but it didnt helped.
do i have to name these different, like (body('get_ a_row).....?
Hi!
Please rename your 'Compose' action blocks exactly the same way I did in my example and give it a try
Thanx!
Proud to be a Flownaut!
Sadly the same Problem no valid reference. Maybe you can show how you store the dates in the first actions and try it like that
Hi!
I just typed them in my first two Compose action blocks:
Hope this helps
Proud to be a Flownaut!
Thank you, yes that helped. Now i build that as well into my flow. The result is that i have the same steps like you, but i used my excel fields for the first steps instead of tipping the dates. And it works. And it makes no difference if a month has 30 or 31 days.. Nice Thank you.
I have two column in my excel, column : created and updated.
format type of both column is date.
Now, I want to perform subtraction between both column.
for that i have created 2 variable named as var1 and var2 and type of var1 and var2 is integer
and i have set the value for var1 is created and var2 is updated
Now i dont have much idea how to proceed, please help
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Read the latest about new experiences and capabilities in the Power Automate product blog.
At the monthly call, connect with other leaders and find out how community makes your experience even better.
User | Count |
---|---|
22 | |
20 | |
9 | |
8 | |
7 |
User | Count |
---|---|
39 | |
30 | |
25 | |
23 | |
12 |