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.
I'll put something together as an example for you for guidance...
@WillPage - Thank you for the explanation. I see how far I'll get with trying to build the solution by using your instructions.
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.
close of business. and yes, it's complicated, but you have a complicated requirement so.. 🤷‍♂️
@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.
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.
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.
Michael E. Gernaey
497
Super User 2025 Season 2
David_MA
436
Super User 2025 Season 2
Riyaz_riz11
244
Super User 2025 Season 2