Wrong date format while using Power Automate

Stanley Nichols

I went back and forth trying to decide if I should post in a Power Automate Forum or this one so I figured I'd try here first.

I have a very simple Power Automate flow. The goal is it grabs employee name and termination date from an email being sent and insert it into a smartsheet row.

Everything works as intended minus the date, the format appears to be off. So I viewed the outlput of my variable and it is showing as:

but, smartsheet show it as:

The expression I am using to generate this is:

formatDateTime(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'effective Date: '),16),10), 'M/d/yyyy')

I have verified my sheet column is a Date and I have also tried changing the date format within Smartsheet.

Any ideas would be great!

Leibel S

The format you would need to send to Smartsheet is:

yyyy-MM-dd

Stanley Nichols

The format you would need to send to Smartsheet is:<\/p>

yyyy-MM-dd<\/p>","bodyRaw":"[{\"insert\":\"The format you would need to send to Smartsheet is:\\nyyyy-MM-dd\\n\"}]","format":"rich","dateInserted":"2023-02-22T19:12:09+00:00","insertUser":{"userID":127476,"name":"Leibel S","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20S","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T20:51:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363678#Comment_363678","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/363678#Comment_363678

I appreciate your response!

So I modified my expression to:

formatDateTime(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'effective Date: '),16),10), 'yyyy-MM-dd')

To go with the format you suggested. Output of Power Automate is:

However, the inserted row is still the same:

Is there anything else you can think of I am doing wrong?

Stanley Nichols

After reading up on Smartsheets API documentation I have figured it out. According to Smartsheet:

The Smartsheet API returns all dates and times in theUTCtime zone inISO-8601format, that is, YYYY-MM-DDTHH:MM:SSZ. If you are specifying a date and time, you should also send that information in ISO-8601 format. If a date/time needs to be displayed to an end-user in their local time zone, you must do the conversion using the user's time zone, which you can obtain bygetting the current user.

So, changing my compose expression to:

formatDateTime(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'effective Date: '),16),10), 'yyyy-MM-ddTHH:mm:sszzz', 'UTC')

Then had this as an output:

I also modified my Set Date Variable within Power Automate to this:

trim(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'Effective Date: '),16),10))

Which outputs our end result:

I appreciate the help and I apologize I should have viewed the API docs more in depth prior.

Thanks!