cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Naz
Advocate I
Advocate I

Format string to date/time format

Im retrieving and parsing JSON that has a date/time in a string format ("29/02/2020 04:46:01"). The JSON passes the value to an add row to Excel table function, which imports it as a string.

 

I've tried using @formatdatetime, but it gives an error stating 'In function 'formatDateTime', the value provided for date time string '29/02/2020 04:46:01' was not valid. The datetime string must match ISO 8601 format.'."

 

Is there any way to either:

1. Parse the JSON so that the field is parsed as a date/time value?

2. Convert the string output from the JSON to a date/time format that is recognised by Excel?

7 REPLIES 7
Jcook
Super User III
Super User III

Hello @Naz

Can you try this:
formatDateTime('29/02/2020 04:46:01', 'dd/MM/yyyyTHH:mm:ss')

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





@Jcook,

doesn't work.

Gives error " 'In function 'formatDateTime', the value provided for date time string '29/02/2020 04:46:01' was not valid. The datetime string must match ISO 8601 format.'."

Jcook
Super User III
Super User III

Hi @Naz

Can you post a screenshot of the action where you are using this expression?

Can you try this also:
formatDateTime('03/15/2018 12:00:00', 'yyyy-MM-ddTHH:mm:ss')

That is from Microsoft docs directly. I just want to know if it works for you.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Here's the screenshot.

 

image.png

 Used with a Compose function for now.

 

Tried the "yyyy…" format too. Does not work. Throws the same error.

SatishPanwar
Frequent Visitor

The problem is with date format DD/MM "29/02/2020 04:46:01". If you pass MM/DD to formatdatetime, it will be fine.

"02/29/2020 04:46:01".

Here is what you can do and it works good.

concat formula = concat(variables('txtArray')[1],'/',variables('txtArray')[0],'/',variables('txtArray')[2])

formatdatetime formula = formatDateTime(variables('c'),'dd/MM/yyyy hh:mm:ss')

 

 
 

Annotation 2020-03-01 173552.jpg

v-litu-msft
Community Support
Community Support

Hi @Naz,

 

@SatishPanwar is correct, the MM should in the front of DD, you could refer to ISO 8601 to make sure the format valid.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-litu-msft
Community Support
Community Support

Hi @Naz,

 

Has your problem been solved?
If your problem has been solved, You could go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best Regards,
Community Support Team _ Lin Tu

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (77,753)