cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Galazus
Frequent Visitor

Help with using formatDateTime and AddDays utcNow expressions

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.

 

formatdate1.JPGformatedate2.JPG

I have attached the working flow with incorrect date format

 

Sorry if the questions seem too basic for you guys.

3 ACCEPTED SOLUTIONS

Accepted Solutions

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

View solution in original post

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

View solution in original post

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

 

Galazus_0-1659535653879.png

 

View solution in original post

10 REPLIES 10
MarconettiMarco
Super User
Super User

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

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?

 

 

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
Frequent Visitor

failed2.JPGfailed.JPGsendemail.JPGfilter1.JPGduedate.JPG

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
Frequent Visitor

Hi members,

 

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

 

Thank you.

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

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

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.

 

Galazus_1-1659521833602.png

 

 

Output email is below.

 

Galazus_0-1659521676880.png

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_2-1659521976696.png

 

Thank you.

 

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

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

 

Galazus_0-1659535653879.png

 

Helpful resources

Announcements
Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (1,850)