Help with using formatDateTime and AddDays utcNow expressions

[Galazus]

Hi members,

I'm a newbie to power automate and trying to build some flows.

My task is to build a flow that sends a user(POC) an email when documents in a SharePoint doc library are due for review, the first email would go out, maybe 30 days before the review date, and then the second one or last 7 days before the review date (DueDate).

I'm building the flow in bits, and at first testing- the email is not showing in the correct dd-mm-yyyy format.

That is the sample email

"Subject: ! IMPORTANT! The following document is due for Review
Importance: High

The file "Demo document" is due for review on 2022-08-23
Please go to the SharePoint library to review the files using by clicking here."

My questions;

1) How can I correctly use the formatDateTime expression? I have a column named(DueDate) in the sharepoint library that contains the dates when the documents should be reviewed.

2) How can I use the AddDays expression to trigger the flow when it is 30 days from DueDate?

I've been searching for answers here but all expressions I've tried keep giving errors.

I have attached the working flow with incorrect date format

Sorry if the questions seem too basic for you guys.

[MarconettiMarco]

Hello @Galazus ,

here the answers:

1) "How can I correctly use the formatDateTime expression? I have a column named(DueDate) in the sharepoint library that contains the dates when the documents should be reviewed": please use the following expression: 

formatDateTime('<your_value>', 'dd-MM-yyyy')

2) How can I use the AddDays expression to trigger the flow when it is 30 days from DueDate?: please use the following expression in the Filter Query instead of 2022-08-23: 

formatDateTime(addDays('<your_value>',-30),'dd-MM-yyyy')

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[Galazus]

Thank you.

I will give it a go and update this thread.

However I'm thinking, should my custom column "DueDate" be my value?

.eg. formatDateTime('DueDate', 'dd-MM-yyyy'). I remember trying it before posting here and it failed.

And where in the flow should these expressions sit?

[MarconettiMarco]

Hello @Galazus ,

you should select your value from the dynamic content.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[Galazus]

I have given it the first go but still no luck- I know the syntax is wrong- see attached.  I need to figure out how the DueDate column from my SharePoint library can be used as a string in that formula 

formatDateTime('<your_value>', 'dd-MM-yyyy')

[Galazus]

Hi members,

Does anyone else have any further tips or examples to help me out?

Thank you.

[MarconettiMarco · Accepted solution]

Hello @Galazus ,

please use the following expression:

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

Instead of "DueDate", please put the SP list unique column name

Here how to get the internal name:

https://www.sharepointdiary.com/2021/01/get-field-internal-name-in-sharepoint-online-using-powershel....

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[v-bofeng-msft · Accepted solution]

Hi @Galazus ,

Please try:

ReviewStatus eq 'Live' and DueDate le '@{addDays(utcNow(),30,'yyyy-MM-dd')}T23:59:59' and DueDate ge '@{addDays(utcNow(),30,'yyyy-MM-dd')}T00:00:00' 

Best Regards,

Bof

[Galazus]

Hi Marco,

Thank you again for your time.

I've tried that using formatDateTime(item()['DueDate'],'dd-MM-yyyy') and it worked. My SP list unique column name was the same since I have no space between the 2 words.

That is where I put the expression in the email body.

Output email is below.

When I get time later today, I will play with the expression to calculate the duedates-30 days.

At the moment I'm only filtering using the ReviewStatus column without the DueDate Column.

[Galazus]

Thank you.

I will give it a go when I get a moment.

[Galazus · Accepted solution]

Thank you, this solved the issue of working out the exact due dates.