Flow to calculate elapsed days since List item modified

[Anonymous]

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.

[v-bacao-msft · Accepted solution]

Hi @Anonymous,

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:

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/135306

Please take a try.

Best Regards,

Barry

[Anonymous]

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

[Anonymous]

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.

[v-bacao-msft]

Hi @Anonymous,

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

[Anonymous]

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

[starcou]

You save my day! Thank you so much!

[Anonymous]

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.