Get calendar event length (in minutes)

[jgarr]

I'm using the O365 upcoming calendar event to trigger my slack status (via HTTP webhook) when a meeting is going to start. https://docs.microsoft.com/en-us/connectors/office365/#onupcomingeventsv2

There is no trigger for when a calendar event is going to end so instead I want to set the slack status to automatically expire after the length of the meeting. In order to do that I need to get the length of the meeting in minutes (e.g. 60)

I was trying to do some math on start time and end time but I couldn't find a way to convert the time into epoc or similar numerical format to be able to do addition on it.

Can a variable be added to the triggerBody or is there another way someone can think to get the value I need?

[MattWeston365 · Accepted solution]

Hi @jgarr I've had to use a bit of maths to calculate the difference between the start and end time. For ease of explanation I've broken it down into separate actions, but the expression could be combined if you wanted to consolidate it.

From my trigger, I'm going to need the Start time and end time

Variable 1: Date Difference

I have used this expression to subtract the number of ticks for the end date and start date. In a 30 minute meeting I have returned the following value: 18000000000

sub(ticks(triggerBody()?['End']),ticks(triggerBody()?['Start']))

Variable 2: Seconds

I have used another expression to work out the number of seconds which have been returned from the number of ticks. Based on the 30 minute meeting, this has return 1800 seconds

div(variables('DateDiff Ticks'),10000000)

Variable 3: Minutes

I have used a calculation here to divide the number of seconds by 60 to calculate the number of minutes, e.g. in my example it would return 30 minutes. You can then use this to perform the rest of your Flow.

div(variables('Difference Seconds'),60)

If you have found this post useful, please give it a thumbs up. If it has answered your question, please accept it as the solution so others can benefit.

@MattWeston365

[jgarr]

This is simultaniously crazy and awesome! Thanks for replying.

quick question. Is there any reason to do this in three steps instead of combining it into 1 or having 2? I couldn't find a reason to not do div(div(variables('DateDiff Ticks'),10000000),60) but maybe there's something I missing with your approach.

[MattWeston365]

No reason for three steps other than illustration. Feel feee to consolidate it

[Jason-E]

Is there a way to use the "Filter Query" function of "Get calendar view of events (v3)" to only retrieve events shorter than a given value (i.e., 8 hours).  Essentially, trying to avoid any entries that either span the entire day or span multiple days.

UPDATE FOR CLARIFICATION:  I am specifically referring to the ODATA Filter Query field in "Get calendar view of events (v3)".   I have written "apply to each" (loop) and "condition" (if/else) logic to implement filtering. However, that results in all items being queried and a much larger result set then filtering it out at the "Get" events action would.

[spmofulton]

I am still very new to power automate, but I think there might be a couple of ways.  Primarly you can do this by setting  skip/next for loops, terminate if its just a few emails at a time, and conditional/regular filters/try-catch if your writing everything to a data-tables or sql first then filtering.

I do have a couple of questions about your question, so I can give specific solutions.

1. Question are you doing this in a loop or not?
2. Question is this on desktop or the cloud?  As the functions seem to have different names on desktop and in the cloud.

Things I found:

https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Power-Automate-Fundamentals-44-Usa...

https://www.shahryarsultan.com/2022/05/power-automate-functions-skip-function.html
https://www.youtube.com/watch?v=AOsQ0XUiBtc

https://www.spsimply.com/home/2018/12/6/flow-let-a-workflow-continue-after-a-step-fails
https://www.matthewdevaney.com/3-power-automate-error-handling-patterns-you-must-know/
https://blog.enterprisedna.co/do-until-loop-control-in-power-automate/