Running a flow using a Calculated Date field in SharePoint

[rth49]

I am trying to set up a flow that looks at a Calculated Date column in a SharePoint Library.  I want to send an email if the Calculated Date matches today's date.  It is an Automated flow that runs daily.  I am using suggestions from this Community, but the flow is not working.  Any help will be appreciated.

[RobElliott]

@rth49 it would be helpful if you could post a screenshot of your flow so we can see where the problems might be.

Rob
Los Gallardos

[rth49]

Thanks Rob,

I thought I did, but it didn't post.  What is the best way to post a screen shot.  Or, what type of file does it need to be?

[rth49]

[PowerBack19]

Have you looked at the output from the SharePoint date? Just to test the value(s) you are seeing.

Also, have you tried to format the date from SharePoint to be the same format as the utcNow()? This way both dates are in the same format when comparing in the condition. 

Additionally, I have run into issues within Power Automate that required using the "Convert Time Zone" action. In certain scenarios, the date in SharePoint could be different than the utcNow() date in Power Automate which could cause your condition to not be met.

[v-litu-msft]

Hi @rth49,

According to the condition of the Flow, it will send an email if the date is (greater than or equal to) and (less than) utcNow, it is a logic error, the two scenarios couldn't exist at the same time. At the beginning you said you want to send an email if the Calculated Date matches today's date, so maybe this is what you want:

formatDateTime(item()?['RevalidationDate'],'yyyy-MM-dd')

utcNow('yyyy-MM-dd')

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

[rth49]

Thank you for trying to help me, I have struggled with this for a couple months.  New to Flows.  I used the expression you suggested and I received the error below.  Is my action correct above the condition?  This SharePoint is a Library, not a list.

[v-litu-msft]

Hi @rth49,

Could you please show the name of the column contains "Revalidation", the RevalidationDate in input just a fake name for test, it should append the real name of the SharePoint column. 

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

[MattWeston365]

Hi @rth49 

The formatDateTime expression requires the date and time to be cast as a string in order for it to work, whereas when the date comes back from SharePoint it is a datetime data type.

A simple update to the expression that @v-litu-msft posted should help you out:

formatDateTime(string(item()?['RevalidationDate']),'yyyy-MM-dd')

[rth49]

Hi,

This is how the column is set up in Library Settings:

[rth49]

Thanks Matt, but I am still getting an error.  I copied and pasted your expression below and I still get an error:

[MattWeston365]

Hmmmm, I'll jump onto SharePoint shortly and see if I can recreate the issue. If you get this message in the meantime, can you post a screenshot of the output of the date field in your automation please?

[MattWeston365]

Hi @rth49 just an update, I've created the SharePoint calculated column and have created a condition on it. My condition therefore looks like this:

In this condition, I have done a formatDateTime on the UTC date as well, so it's worth checking to make sure that your comparison is also in the right date time format.

[rth49]

Thanks Matt,

It failed.  The flow fails no matter what we try.  I am going to go back to the drawing board and see if I can come up with a different solution using flow a different way.  I have been trying to get a solution going this route for several months and advice from others on your team with no luck.  I think this would have worked fine if flow had the =today option.

[RobElliott]

The aspect that puzzles me is why you are using a calculated column for the revalidation date in your SharePoint list column. You don't need to: an ordinary date/time column would be sufficient with the calculation/date comparison done within a PowerAutomate expression. I never use calculated columns in SharePoint for this sort of thing. I'll try to post an example of how I would do it a bit later.

Rob
Los Gallardos

[rth49]

Thanks,

I had that suggested a few months back, but they didn't explain how that could be configured.  I tried to use the Last Modified date and add 330days, but that wasn't possible going that far out in flow.  If flow figure out 330 days that would be great.

This is the scenario:

• We have a SharePoint Library with 4,000 Word documents.  Some of them get updated/modified frequently, some of them don't.
• We want to make sure each document is reviewed/revalidated at least once a year.
• I wanted to daily use the last modified date, add 330 days and automatically send an email to the owner of the documents asking them to review/revalidate the content is current.

I am not sufficient with Flow, so I had to ask for help.  But I am learning a lot!!!

Thanks for the advice and help

Rick

[rth49]

Hi Rob,

Have you had a chance to think any more about this.  I replied before with the scenario of what I am trying to do.  Not sure if that helps you or not.

Thanks,

Rick