Subtract two excel dates

[MarvinBier]

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.

[efialttes]

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!

[MarvinBier]

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

[efialttes]

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!

[efialttes]

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

[MarvinBier]

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.

[MarvinBier]

okay i corrected verfassen 3 but it didnt helped.

[MarvinBier]

do i have to name these different, like (body('get_ a_row).....?

[efialttes]

Hi!

Please rename your 'Compose' action blocks exactly the same way I did in my example and give it a try

Thanx!

[MarvinBier]

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

[efialttes · Accepted solution]

Hi!

I just typed them in my first two Compose action blocks:

Hope this helps

[MarvinBier]

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.