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

6 REPLIES 6
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

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!

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (66,898)