cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daryl_K
Helper II
Helper II

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
v-bacao-msft
Community Support
Community Support

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

7 REPLIES 7
v-bacao-msft
Community Support
Community Support

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.

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

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.

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.

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

You save my day! Thank you so much!

NewNovichoke
Frequent Visitor

This solution doesn't work because you want a daily flow to re-calculate the Age of a list item.  Formulas in sharepoint cannot refresh this field unless the item has been modified. 

 

Well, the days since last modified is going to be yesterday, because that is when you updated the Age of the list item; it's not necessarily the last time the list item has been worked on.  

 

A better way is to not store the Age field in Sharepoint.  If you use PBI, you can calculate in there: Last modified date - Today without having to store and increment a variable in sharepoint which will throw your formula off. Not as handy, but it's what works.

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,483)