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

(0) Share

Report

Posted on by Ghada Mehrez

257

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

Categories:

Building flows

I have the same question (0)

All responses (21)

Answers (1)

Mira Ghaly 11,413 Moderator on at

Like (0)

Report
@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'

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (1)

Report
@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)

Was this reply helpful? Yes No
Ghada Mehrez 257 on at

Like (0)

Report

@Mira_Ghaly I got error message :

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@gme-eg

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

Was this reply helpful? Yes No
Ghada Mehrez 257 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (1)

Report

@gme-eg

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

Was this reply helpful? Yes No
Ghada Mehrez 257 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@gme-eg

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

Was this reply helpful? Yes No
Ghada Mehrez 257 on at

Like (0)

Report

Was this reply helpful? Yes No