Wrong date format while using Power Automate

Stanley Nichols
Stanley Nichols ✭✭
in API & Developers

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:

image.png

but, smartsheet show it as:

image.png

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!

· Share on FacebookShare on Twitter

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    The format you would need to send to Smartsheet is:

    yyyy-MM-dd

    · Share on FacebookShare on Twitter
  • Stanley Nichols
    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:

    image.png

    However, the inserted row is still the same:

    image.png

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

    · Share on FacebookShare on Twitter
  • Stanley Nichols
    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:

    image.png


    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:

    image.png

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

    Thanks!

    · Share on FacebookShare on Twitter

Categories