Answered

ISO 8601 format error when using Ticks function

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello everyone,

I have a data set, in which there are an average of 190 items. Each item comes with a date and time. I need to subtract incoming dates and times from instant dates and times. Therefore, I use the ticks function for both dates separately, while I do not have a problem when I do it for the current date, I get an error when processing the date and time data from my dataset with the ticks function.

The sample date and time data I received is as follows: '20230426 000900.000'

The ticks function content I use is as follows: ticks(formatDateTime(item()?['X']),'yyyyMMddTHHmmss.sss')

ticks(formatDateTime(item()?['X']),'yyyyMMddTHHmmss')

ticks(formatDateTime(item()?['X']),'yyyy-MM-ddTHH:mm:ss')

i tried each sample one by one but i always got the same error

The error message I got is as follows: Unable to process template language expressions in action 'Compose_5' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '20230427 000200.000' was not valid. The datetime string must match ISO 8601 format.'.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Pstork1 69,129 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
YOur sample date/time isn't in ISO 8601 format so it can't be converted to a Date/Time by the format string. YOu are trying to use FormatDateTime to take a non-iso8601 string and make it an Iso 8601 string. FormatDateTime isn't designed to do that. Since Power Autoamte doesn't have a Text Format function you'll need to use a combination of Concat() and Slice(). Something like this

concat( slice(item()?['X'], 0, 4), '-', slice(item()?['X'], 4, 6), '-', slice(item()?['X'], 6, 8), 'T', slice(item()?['X'], 9, 11), ':', slice(item()?['X'], 11, 13), ':', slice(item()?['X'], 13, 19), 'Z')

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

You should consider function parseDateTime() for this.
Also function dateDifference() might be useful here.

Was this reply helpful? Yes No
Pstork1 69,129 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

That's a good thought, but I don't think ParseDateTime will work since this isn't a locale specific date time string. The input for parseDateTime has to be a recognizable datetime string for a particular locale.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you for your suggestions. The problem is solved when you use the concat and slice function as you said.

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
parseDateTime('<timestamp>', '<locale>'?, '<format>'?)
parseDateTime('20230426 000900.000', 'es-es', 'yyyyMMdd HHmmss.000')
returns 2023-04-26T00:09:00.0000000

dateDifference( parseDateTime('20230426 000900.000', 'es-es', 'yyyyMMdd HHmmss.000'), parseDateTime('20230501 130000.000', 'es-es', 'yyyyMMdd HHmmss.000') )
returns 5.12:51:00

Was this reply helpful? Yes No
Pstork1 69,129 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I stand corrected. I didn't think it would work on a string without a more obvious format.

Was this reply helpful? Yes No