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

[Anonymous]

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')

 

Thanks in advance.

[AlanPs1]

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?

[Anonymous]

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.

[AlanPs1]

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.

[AlanPs1]

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.

[ScottShearer]

@Anonymous:

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

[Anonymous]

[Anonymous]

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.

 

[ScottShearer · Accepted solution]

@Anonymous:

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:

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

Scott

[Anonymous]

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.

[ScottShearer]

@Anonymous:

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

Scott

[Anonymous]

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

[Anonymous]

Worked like a charm, thanks a lot Scott.

[CindyZ]

@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!  

[ScottShearer]

@CindyZ 

Thanks! I'm glad I could help.

[MHidayat]

Hi Scott, 

I've done what you demonstrated, but I get an error notification "The datetime string must match ISO 8601 format"

can you give me suggestion?