Wrong date format while using Power Automate
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!
Answers
-
Leibel S ✭✭✭✭✭✭The format you would need to send to Smartsheet is:
yyyy-MM-dd
-
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?
-
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!
Categories
There's nothing we can do as users. It would require a rebuild of that system. I'm pretty sure it's resource intensive, and runs on a schedule performing the automations.<\/p>"},{"commentID":388346,"body":"
No problem. Sorry I didn't have a way to help solve your problem. <\/p>
Having to wait is especially frustrating for me when I'm giving a demo, but that's not the only time I'd like them to execute faster. <\/p>
Consider submitting a Product Idea here (if a search for an already made suggestion isn't available for you to upvote. <\/p>
