SharePoint Online Date - Subtract days and save to new field

[volkjr21]

I am trying to run a flow that will send notifications to document owners 31 days prior to an expiration date. The first part of the flow is to dynamically create that expiration date for the end users. I will have the flow trigger daily. I am running the Update File Properties step and using this formula to try and save the expiration date in a new column in the document library: formatDateTime(addDays(triggerBody()?['Date Expires'],-31),'mm-dd-yyyy'). 

I get this error when it runs: 

Suggestions? I have verified that the field is not empty that I am trying to subtract from. It is a date column in SharePoint Online.

[Sundeep_Malik]

Hey @volkjr21 

The error says it's null, but as you said you have verified it as it's not null.

Can you make a compose action, add it above update file properties action and add the following expression in it just to confirm that the date is coming and not null value is coming in it.

triggerBody()?['Date Expires']

And post screenshot of it here.

One more thing in the expression that you have used write Capital MM, because small mm are used for minutes and capital MM are used for months.

[CFernandes]

Hey @volkjr21 

Your expression looks correct. Did a quick test with a new list

formatDateTime(addDays(triggerOutputs()?['body/Created'], -31), 'MM-dd-yyyy')

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

[ManishSolanki]

Hi @volkjr21 

Please try using the below formula:

formatDateTime(addDays(triggerBody()?['DateExpires'],-31),'yyyy-MM-dd')

If that doesn't work then check the internal name of the column and replace in above expression. To find internal name, go to List settings and click on the 'Date Expires' under Column section. You will get the internal name of the field in the URL as the 'Field' Query parameter (after Field=) as shown in the below screenshot:

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[volkjr21]

I updated the formula to have MM for the month rather than mm. I also updated the column name as I had been testing and it didn't match the internal name. It is now using the internal name with no spaces and I am still getting the same error. 

When I run the Compose action, I get links to view the inputs but it shows up as a blank page as well as the outputs. 

Here are the forumulas:

Compose: triggerBody()?['DateExpires']

Update: formatDateTime(addDays(triggerBody()?['DateExpires'],-31),'dd-MM-yyyy')

[volkjr21]

I updated my formula as I was testing and changed it away from the internal field name. I have updated it now and still get the error. 

[Sundeep_Malik]

@volkjr21 

If you see the compose, it's saying click to download that means it's a null value. So that expression is giving you a null value.

So, you will have to do something for that null value. 

[volkjr21]

This is what the condition looks like:

When I add that field directly into an email or something it works fine but if I use an expression at all, it comes back as null. The Condition looks at a column that is filled out on all documents in the library. From there, I had assumed that I could use that DateExpires field but it doesn't seem to be the case. When I try to build my expression, these are all the options I get for Dynamic Content.

[volkjr21 · Accepted solution]

I took a different path for this and initialized a variable before getting into my condition. Prior to the update of the columns on the file, I set the variable to the DateExpires field. I then subtracted my days in the formula and it is working like it should now. Seems like a crazy way to just subtract days from one field but that seems to be the case with most of what I want to do in Power Automate compared to a tool like Nintex.

This is my new formula: addDays(variables('varDateExpires'),-31)