cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gme-eg
Helper IV
Helper IV

Use expressions in flow actions to filter by Year and calculate hours between start and end time.

Please I have SharePoint list synced with the office outlook 365 calendar using power automate flow as below.

I need to build expressions in the Filter Query to achieve these needs:

1- I have hours column in the list I need to add an expression to it in order to calculate the hours between start and end time in office outlook calendar.

2- I need to add expression to filter events synced with office outlook 365 to sync the only years 2020 and 2021.

 

 2021-04-05 09_49_44-Daily Operation Follow-UP Meeting _ Microsoft Teams.png

1 ACCEPTED SOLUTION

Accepted Solutions
Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

To get the hours please try this expression instead:

so if it is 30 minutes it will return 0.5

div(float(div(div(mul(sub(ticks(items('Apply_to_each')?['end']),ticks(items('Apply_to_each')?['start'])),100),1000000000),60)),float(60))
If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

21 REPLIES 21
Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

 

For the filtration to get years 2020 and 2021 Please use below expression on ODATA Filter on the Filter Query Property of the Get Events

 

start/DateTime ge '2020-01-01T00:00:00.0000000'

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
v-litu-msft
Community Support
Community Support

Hi @gme-eg,

 

To make a supplement.

If you want to calculate hours between start and end date, you could use ticks() function to approach that, here is a post with the solution by xida, it could help you:

Solved: how to get difference between two dates in flow - Power Platform Community (microsoft.com)

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

As mentioned by @v-litu-msft  the expression that you should use in your case is as below, Please add a compose data operation action before create Item and use the below Expression  and this will return the difference in minutes

div(div(mul(sub(ticks(items('Apply_to_each')?['end']),ticks(items('Apply_to_each')?['start'])),100),1000000000),60)

Mira_Ghaly_0-1617614327701.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

@Mira_Ghaly I got error message :

2021-04-05 11_50_39-Edit your flow _ Power Automate.png

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

Just add it on the filter Query directly without using the Expression pane.

Mira_Ghaly_0-1617616445896.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Now I followed all steps and inserted the Filter Query directly with no errors but when I save the flow it gave me this error it seems related to apply to each step.

2021-04-05 11_58_48-Daily Operation Follow-UP Meeting _ Microsoft Teams.png

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

Please move the compose data operation inside the apply to each loop

Mira_Ghaly_0-1617616964039.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

@Mira_Ghaly yes now it works the events of 2020 and 2021 only syncs but in terms of the Hours Column, it didn't calculate the hours between start and end time.

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg

Can you open the flow history and check the output of the compose data operation

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

2021-04-05 12_22_24-Daily Operation Follow-UP Meeting _ Microsoft Teams.png

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

So this is returning the minutes duration , how is the field type in sharepoint you want to save the duration in?

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

2021-04-05 12_33_26-.png2021-04-05 12_32_54-Service Operations - Trial Sync Outlook with SP List001 - All Items.png

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

To get the hours please try this expression instead:

so if it is 30 minutes it will return 0.5

div(float(div(div(mul(sub(ticks(items('Apply_to_each')?['end']),ticks(items('Apply_to_each')?['start'])),100),1000000000),60)),float(60))
If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

I updated the expression as above and runned the flow again but the hours column still not calculating hours.

 

I added the expression to the hours column in the flow but not working as well.

2021-04-05 12_50_31-Edit your flow _ Power Automate.png

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

What is the type of the hours column , you showed me the start time not hours?

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

it is Number2021-04-05 13_01_46-Service Operations - Trial Sync Outlook with SP List001 - All Items.png

Mira_Ghaly
Dual Super User II
Dual Super User II

@gme-eg 

can you uncheck this?

Mira_Ghaly_2-1617620773271.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

@Mira_Ghaly I unchecked it but still no calculations 😞

Mira_Ghaly
Dual Super User II
Dual Super User II

 @gme-eg

Did you check flow history?

Is it calculating correct in the compose data operation?

Also instead of calculating the duration from Powerautomate have u tried using calculated column in sharepoint? 

 

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (63,877)