cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Get time difference in hour

Hello, I am trying to get the time difference between two dates from a sharepoint list item.

 

When I create a new item I am sending an email with the item's information. 

 

Now I want to send in the email body the difference between StarDate and EndDate in hour format (hh:mm)

 

Any help would be great, thanks 

7 REPLIES 7
Highlighted
Super User III
Super User III

Re: Get time difference in hour

Hi @Anonymous ,

 

There is no date difference function available in flow to calculate the difference between two days. You need to use the ticks function, then subtract the two values and finally divide the difference by a fixed value to get the difference of days. Please see below.

 

First declare three variables.

 

dt1.png

 

Then add the SharePoint GetList 

dt2.png

 

Next add the loop

 

dt3.png

 

Here above the ticks expression is for the start date is as follows.

 

ticks(items('Apply_to_each')?['StartDate'])
 
and for the end date
 
ticks(items('Apply_to_each')?['EndDate'])
 
Final step is 
dt4.png
Here the function expression for calculating the difference of days is
 
div(sub(variables('EndDate'),variables('StartDate')),864000000000)
 
Try the above steps and let me know the results.
 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

 

Thanks

 
 
 
 

 

 

 

 

 

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Super User II
Super User II

Re: Get time difference in hour

@Anonymous 

Hi there!  So, I went a different route than @abm so you'll have your choice of solutions (great place to be Man Happy) - 

First, a bit about date math in Flow:  It's weird.  Like seriously weird.  But it's not just Flow, I guess a lot of computer date math works like this.  Check out this article on the specifics of how this all works, I think it's a good start for the beginner.

 

For the solution, I did take the same path in converting the two dates to "ticks".  I don't think there is any way around that yet.  But, instead of dividing everything by 864000000000, which is the number of ticks in a single day...I used 600000000, which converts everything to 'minutes' instead.  So, just like @abm , initialize an integer variable for each date, and for the value, try using  DIV(ticks(utcNow()),600000000)  (but replace the red part with each respective date/time).

 

So, now you've got two variables...  DateTime001 and DateTime002 (or whatever you call it) both DateTimes are actually numbers counting the number of minutes since New Year's Day, 1601 (no joke, I said it was weird).  Now, we initialize a third (integer) variable that will be the difference between the two...that expression will look like this: 

sub(variables('DateTime001'),variables('DateTime002')) - Again, the red stuff is your actual variable names.

 

This will give you the difference between the two in number of minutes...so now we just need to format it in the hh:mm format.  This is where it gets fun:

Credit to John Liu for this idea, but basically we are going to add minutes to midnight and have it return that time...which will look like hours and minutes (so, 130 minutes will look like 02:10).  That expression looks like this:

addMinutes(startOfDay(utcNow()), variables('varTimeDiff'), 'hh:mm')  - The red is the variable you created above.  You can see we are just adding a number of minutes to midnight ('start of day') and then formatting that in an hh:mm format.

Pretty cool, I think.

Either way, keep us posted.

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Highlighted
Community Support
Community Support

Re: Get time difference in hour

Hi @Anonymous ,

 

Have you considered creating a calculated column?

Please check this online doc and see if it helps:

https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14)#date-and-time-formulas

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User
Super User

Re: Get time difference in hour

Thank you for this write up @edgonzales . Really helped me out. 

Scenario:

  • Customer wants a Flow to run every 30 mins at :20 and :50 past the hour which grabs last 10 days of data
  • I set a Recurrance trigger to Every 30 mins beginning at 2019-10-02T09:50:00
  • Used your example in a Compose action to determine number of hours from current hour
    • div(sub(ticks(utcNow()),ticks(startOfHour(utcNow()))),600000000)
  • Initialized two variables: varDeltaDuration (# of days to go back and look for data) and varDeltaTimestamp (time of day on the oldest day of delta lookup)
  • Created a Condition to check IF number of minutes from current hour < 50, set varDeltaTimestamp to :50 mins past current hour ELSE set varDeltaTimestamp to :20 mins past current hour
  • Run the query

Thanks again!

Highlighted
Frequent Visitor

Re: Get time difference in hour

Hi,

Can you please tell me what i should select in the apply to each field (*Select an output from previous steps) after setting 3 variables.

DateDifference.jpg

Highlighted
Frequent Visitor

Re: Get time difference in hour

Hi,

Could you please explain how the formula is linked to the column StartDate and EndDate in the sharepoint list?

ticks(items('Apply_to_each')?['StartDate'])
 
and for the end date
 
ticks(items('Apply_to_each')?['EndDate'])
Here the function expression for calculating the difference of days is
 
div(sub(variables('EndDate'),variables('StartDate')),864000000000)- Should i set the variable in the next step or inside Apply to each?

 

DateDifference1.jpg

Highlighted
Super User II
Super User II

Re: Get time difference in hour

@vniranjanrao 

Hi there - So, the Apply to each is weird and may be causing you some trouble.  It's difficult to troubleshoot without the context of your specific flow.

 

Maybe try posting a new thread with some more details and screen shots and we can help you there?

 

Good luck!

-Ed

 

Helpful resources

Announcements
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.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Top Solution Authors
Top Kudoed Authors
Users online (8,713)