cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daryl_K
Level: Powered On

Flow to calculate elapsed days since List item modified

Is there a relatively simple way to calculate the elapsed number of days from NOW to the Modified column value for a SharePoint list? In this case I am only interested when days of inactivity reaches 3, 4 or 5, so it does not even have to be exact enough to account for time zone differences.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Flow to calculate elapsed days since List item modified

Hi @Daryl_K,

 

There is currently no direct Expression or action in Flow to calculate the difference between two dates.

If you want to the feature to be supported in Microsoft Flow, you could vote for the similar ideal in the forum below:

https://powerusers.microsoft.com/t5/Flow-Ideas/Datedif-function-in-Flow/idi-p/103429

In addition, there is a method you could use for reference. Use the ticks() function to get the difference between two dates and a date, then subtract the two values.

Divide the difference by a fixed value to get two dates. There is a difference of a few days between them.

Expression:

utcNow: 

ticks(utcNow())

Modified: 

ticks(items('Apply_to_each')['Modified'])

Diff: 

div(sub(outputs('utcNow'),outputs('Modified')),864000000000)

Image reference:

65.PNG

You could also refer to the following links, which mention similar methods for reference:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Subtract-dates/td-p/126119

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Flow-Calculate-Date-Diference/ba-p...

Please take a try.

 

Best Regards,

Barry

 

 

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

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Flow to calculate elapsed days since List item modified

Hi @Daryl_K,

 

There is currently no direct Expression or action in Flow to calculate the difference between two dates.

If you want to the feature to be supported in Microsoft Flow, you could vote for the similar ideal in the forum below:

https://powerusers.microsoft.com/t5/Flow-Ideas/Datedif-function-in-Flow/idi-p/103429

In addition, there is a method you could use for reference. Use the ticks() function to get the difference between two dates and a date, then subtract the two values.

Divide the difference by a fixed value to get two dates. There is a difference of a few days between them.

Expression:

utcNow: 

ticks(utcNow())

Modified: 

ticks(items('Apply_to_each')['Modified'])

Diff: 

div(sub(outputs('utcNow'),outputs('Modified')),864000000000)

Image reference:

65.PNG

You could also refer to the following links, which mention similar methods for reference:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Subtract-dates/td-p/126119

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Flow-Calculate-Date-Diference/ba-p...

Please take a try.

 

Best Regards,

Barry

 

 

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

View solution in original post

Daryl_K
Level: Powered On

Re: Flow to calculate elapsed days since List item modified

Thanks Barry. I am just getting back to this but have a basic Q: being a Flow newbie I am getting this message:

 

"The template validation failed: 'The action(s) 'utcNow' referenced by 'inputs' in action 'Diff' are not defined in the template.'."

 

It seems I have to initialize it as a variable, but if so, where? It I put it outside the 'Apply to Each' it won't be recognized. If I put it inside it tells me it must be at a "Higher Level." There is something outside of your screenshot I'd love to see. 🙂

Daryl_K
Level: Powered On

Re: Flow to calculate elapsed days since List item modified

Sorry! It was a dumb typo. It works fine now, but I have a second issue.

 

I want this to iterate through a SharePoint list and check conditions on every entry. At present the Flow runs through and the first list item that satisfies the criteria it follows through on the action and then stops. Ultimately I'd like it to iterate through every entry in the list once every 24 hours and then execute the actions.

Community Support Team
Community Support Team

Re: Flow to calculate elapsed days since List item modified

Hi @Daryl_K,

 

I am afraid I have not understood what your needs are.

Could you provide more explanations about your requirements?

As for "once every 24 hours", you could set Recurrence as trigger and then configure the cycle once a day.

Please provide more details and which should be helpful for me to share a further suggestion.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Daryl_K
Level: Powered On

Re: Flow to calculate elapsed days since List item modified

Barry, it's OK. I ended up figuring out the rest on my own (and through some Google searches) and your original answer gave me what I need to calculate the time difference. I've been testing the Flow and so far it seems to be working great (why I marked your answer as the solution). 🙂 Thank you for your precise response!

 

Not so much a question as a comment I'll add: being a Flow newbie I was rather surprised that specifying a View on the Get Items command only limited the number of columns the Flow looked at. Filters applied in that View (i.e. only show if Column X is TRUE) are ignored. I had multiple conditions and made many Views, which I thought would simplify the Flow. When it turned out that didn't work I ended up doing all the filtering in the Flow, which required dozens of variables and Conditions nested 8 deep!

 

It works, but I am disappointed MS doesn't have the Flow obey View filters too...

Highlighted
starcou
Level: Powered On

Re: Flow to calculate elapsed days since List item modified

You save my day! Thank you so much!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,007)