cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Subtract two excel dates

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.

 

 

MarvinBier_0-1594550208394.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi!

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

Flow_DateInterval_3.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

11 REPLIES 11
Highlighted
Dual Super User III
Dual Super User III

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

 

Hello,

 

1. I store the dates in that following formate

MarvinBier_2-1594555799730.png

 

2. And it looks like that, if i perform get a row

MarvinBier_0-1594555737461.png

So actually the same formate.... but i dont know

 

Highlighted

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!

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

Hi again!

So I stored temporarily both dates you obtain through 'Get a row' in two Compose action blocks:

Flow_DateInterval_1.png

Now I transform them into yyyy/MM/dd format, and finally a calculate the interval

Flow_DateInterval_2.png

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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

Thank you but i still have some problems. I did it like you showed. 

 

MarvinBier_0-1594582433577.png

 

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.

 

Highlighted

MarvinBier_0-1594582862160.png

 

okay i corrected verfassen 3 but it didnt helped.

 

Highlighted

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

Highlighted

Hi!

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

Thanx!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

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

MarvinBier_1-1594584375419.png

 

Highlighted

Hi!

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

Flow_DateInterval_3.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted

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.

 

MarvinBier_0-1594586708807.png

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Users online (7,981)