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

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 the UTC time zone in ISO-8601 format, 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 by getting 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!