cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmistry
Level 8

How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Hi,

 

In a SharePoint list, I have following date fields: ContractEffectiveDate and ContractExpirationDate and I am trying to modify their date format using formatdatetime function to show in mm-dd-yyyy format in body section of send an email action.

 

I am using below expression: However it does not work and my send an email action shows an error, hence flow run fails. Can someone please help me with the correct expression

formatDateTime(triggerBody()?['ContractExpirationDate'],'MM-dd-yyyy')

 Capture.PNG

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

@dmistry:

 

My understanding of your issue is that Flow doesn't like how your are formatting the date field in your email.  If that is the case, try this:

  1. Outside of your loops, create/declare a string variable - I called mine varDate
  2. Immediately before the email action and inside of the loop, set the variable to the date column - I used the modified date.  Don't try to do any formatting here.
  3. In the email body, use the formatDateTime() expression to format the date
    1. Use the variable as the first parameter
    2. My expression looks like this:  formatDateTime(variables('varDate'),'MM-dd-yyyy')

The Flow in the screen shot below runs successfully and the email has the date formatted to your specification:

EmailDate.jpg

 

If this solves your issue, please mark your post as Solved.

 

Scott

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

Scott

View solution in original post

14 REPLIES 14
Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Basically, the error says it expects a string but has gotten Null.

so this : triggerBody()?['ContractExpirationDate'] must equal Null.

 

Can you please go to the failed flow run, expand out some of the inputs and pos them please?


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


dmistry
Level 8

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Hi Alan,

 

There are three items in the list. Only for one of those three items, I am seeing this error in the flow send an email action. For other two items, send an email action is skipped as it did not satisfy the condition in apply each block before send an emai action. Let me know what exact screenshots u need, appreciate your help, thanks.

Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Is it the case that 1 of the date formats are diferent perhaps. Please post the values, the dates and the failed flow run inputs and outputs.

 

Post anything that you think helps paint the picture.


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Also try formatDateTime(triggerBody()['ContractExpirationDate'],'MM-dd-yyyy') (removal of ?)

 

? is to handle null but null cannot be passed in to formatDateTime, it must be a standard time format (not null), but it will be clearer to me if that is relevant if I can get an idea of the inputs and outputs before the send email.

 

I'm not certain that will do it hence I await the detailed screen grabs.


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

@dmistry:

 

Can you please post a screen shot of one of the SharePoint list items on which the Flow failed?  It would help to see the underlying data.

 

Scott

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

Scott
dmistry
Level 8

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Untitled.png

dmistry
Level 8

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Flow.pngOutputs.png

Flow condition is: @AND(equals(items('Apply_to_each')?['ContractExpirationDate'],
formatDateTime(addDays(utcNow(), 150), 'yyyy-MM-dd')),
contains(items('Apply_to_each')?['Days'], '60'))

 

Becuase of this flow condition, the send an email action would only be executed for respective records. So when i tested this, I had three items in the list out of which for 2nd item, send an email action failed becasue of the expression. You can see the list screenshot below.

Untitled.png 

Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

@dmistry:

 

My understanding of your issue is that Flow doesn't like how your are formatting the date field in your email.  If that is the case, try this:

  1. Outside of your loops, create/declare a string variable - I called mine varDate
  2. Immediately before the email action and inside of the loop, set the variable to the date column - I used the modified date.  Don't try to do any formatting here.
  3. In the email body, use the formatDateTime() expression to format the date
    1. Use the variable as the first parameter
    2. My expression looks like this:  formatDateTime(variables('varDate'),'MM-dd-yyyy')

The Flow in the screen shot below runs successfully and the email has the date formatted to your specification:

EmailDate.jpg

 

If this solves your issue, please mark your post as Solved.

 

Scott

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

Scott

View solution in original post

dmistry
Level 8

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Thanks Scott, I will try this out. One confusion, since I have to re-format two date fields: ContractExpirationDate and ConractEffectiveDate, should i initilaize two variables with different names and then set them inside the apply-each block exactly as you showed.

Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

@dmistry:

 

I would suggest using two separate variables as you have suggested.  You could create an array but why make it complicated?

 

Scott

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

Scott
dmistry
Level 8

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Makes complete sense, let me try this out. Appreciate your help, thank you.

dmistry
Level 8

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

Worked like a charm, thanks a lot Scott.

CindyZ
Level: Powered On

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

@ScottShearer THANKS SO VERY MUCH!!!!   I've spent  more than a day and been over tons of different posts trying to resolve this!  Finally, came across yours.  I understood it and it works like a charm!   Thanks so much for taking the time to provide such clear and helpful guidance!  

Highlighted
Super User
Super User

Re: How to format date value in email body to display as MM/dd/yyyy instead of default yyyy-mm-dd.

@CindyZ 

Thanks! I'm glad I could help.

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

Scott

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,505)