cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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.'."

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.

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

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.

 

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (13,147)