0

I've been doing a lot of Power BI work lately and has been a bit since I've had to construct any Flows. In fact, it was still MS Flow before renamed to PowerAutomate.

Anyways, I could have sworn there was a DateDiff() function which is not there now. So I need a bit of help.

I built a scheduled flow to run every morning on all 'Account' records that calculates the number of days until "Renewal Date". As seen below:

enter image description here

The "Days until Renewal Date" field is an int field and "Renewal Date" is a date field. When attempting to use the following expression for "Days Until Renewal Date," it is not saving to the field in the flow and am assuming that bc this function is no longer valid:

enter image description here

Any advice on this would be helpful.

J.S. Orris
  • 4,653
  • 12
  • 49
  • 89
  • Excel has `DateDif()` but I've never seen anything like it in PowerApps. – teylyn Nov 10 '20 at 20:45
  • @teylyn I could of sworn they had a DateDiff() function a couple years ago when it was MS Flow; I could be wrong though. With that being said, I just can't believe MS didn't include a global function pretty much used on any platform like that. – J.S. Orris Nov 10 '20 at 22:45
  • There is indeed a dateDifference() function at this time, but it is actually much more pain than the accepted solution, which works like a charm. – once Jul 21 '23 at 23:49

2 Answers2

5

There is no simple function to calculate the difference between two dates in Power Automate. What you can do instead is

  • create a variable of type integer called varStartDateTicks for the start date using the ticks() function
  • ticks(triggerBody()?['StartDate'])
  • create a variable of type integer called varEndDateTicks for the end date, using the ticks() function
  • ticks(triggerBody()?['EndDate'])
  • subtract the two variable values and divide the result by 864000000000
  • div(sub(variables('varEndDateTicks'),variables('varStartDateTicks')),864000000000)

The result will be the difference in days between the two dates.

Edit after a useful comment by user "once":

Substitute the big number with 36000000000 and you will get hour difference, 600000000 minute difference, 10000000 difference in seconds

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Giving it ago now. Will accept once I get it working. Thanks! – J.S. Orris Nov 10 '20 at 22:46
  • accepted your answer but may you update it to ensure that you convert these to int as I had to do to make work? – J.S. Orris Nov 13 '20 at 16:30
  • Convert what to int? I declared the variables as Integers from the start. There was no reason to convert anything. – teylyn Nov 14 '20 at 01:00
  • I would just note to declare as integers to make the answer a bit more specific – J.S. Orris Nov 14 '20 at 17:53
  • Substitute the big number with 36000000000 and you will get hour difference, 600000000 minute difference, 10000000 difference in seconds. – once Jul 21 '23 at 23:57
2

There is now a dateDifference() function added in Power Automate to avoid these complex expressions steps.

Link to dateDifference reference

Theresa
  • 3,515
  • 10
  • 42
  • 47
Mustaque Ehiya
  • 29
  • 1