Conditions between two times

[Caleb62881177]

Hello, 

I build this flow where I want the condition only to be true when minutes of the SharePoint Created time is between two numbers like 179(greater than) and 270(less than) and it the condition should also take Business Hours(09:00 - 17:00) and, weekends into account.

Example:

Created time Friday 16:50

Output:

The condition is true for 50% when it's Monday and the time between 10:49am &12:20pm

Example:

Created time Friday 16:50

Output:

The condition is true for 75% when it's Monday and the time between 12:19am &13:50pm

Example:

Created time Friday 16:50

Output:

The condition is true for 100% when it's Monday and the time greater or equals 13:50pm

Example:

Created time Monday 07:50

Output:

The condition is true for 50% when it's Monday and the time between 10:59am &12:30pm

Example:

Created time Monday 07:50

Output:

The condition is true for 75% when it's Monday and the time between 12:29am &13:59pm

Example:

Created time Monday 07:50

Output:

The condition is true for 100% when it's Monday and the time greater or equals 14:00pm

Friday greater or equals to 17:00 or any time in weekend is the same calculation as Monday 07:50

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

 

Any help would be appreciated.

[WillPage]

That's hard! Not beyond the bounds of possibility but could be quite time consuming to make it work. What is likely the oldest file you need to calculate the minutes for? Would be much simpler if files aren't likely to be several days old or more.

[Caleb62881177]

@WillPage - The oldest file would be less than a week old.

[WillPage]

That's still a long time to be calculating its age with all those gaps for evenings and weekends. If it was always going to be no older than say, the previous working day you could fairly easily do that calculation, but when it could span multiple days and a weekend it's pretty tricky.

To start, get the created date (without the time) into a variable. Then run a Do until loop and increment the day inside the loop using AddDays until you reach the current date. You loop could be between 0 and 5 iterations long.

Inside the loop, establish whether the current iteration is either the created date or today and increment a variable for excluded minutes like so:

If the current iteration is the created date, increment the excluded minutes being the number of mins from CoB to midnight. If the current iteration is today then increment the excluded minutes as the number of minutes from midnight to start of business. If it's a weekday in between then increment the number of excluded minutes to be both of the above and if it's a weekday the excluded minutes is the full 24hrs, whatever that is 1440 or whatever.

Now, then the loop is finished, calculate the total age of the file by the difference using Ticks like you've already done then subtract the excluded minutes.

[Caleb62881177]

@WillPage  - Your explanation sounds very complicated. I am not sure what CoB means. The ticks expression I found online and I am not 100% sure of what it actually does.

[WillPage]

close of business. and yes, it's complicated, but you have a complicated requirement so.. 🤷‍♂️

[WillPage]

ticks is a function that turns a timestamp into a set unit of time since some epoch, like 1/1/1600 or something. You can use that absolute timestamp to add or subtract the number of ticks between two timestamps to get the difference between them. A tick is a nanosecond so you then have to divide the result by a very large number to get the time difference in seconds.

[Caleb62881177]

@WillPage - Thank you for the explanation.  I see how far I'll get with trying to build the solution by using your instructions.

[Caleb62881177]

@WillPage - I am not sure if I am on the right track.

 

 

[WillPage]

I'll put something together as an example for you for guidance...

[WillPage]

@Caleb62881177  here you go: https://willpage.dev/2023/10/30/power-automate-calculate-working-duration/