Problem with converting date to formatdate

(0) Share

Report

Posted on by Sbsyx

113

Hello,

I have strange of problem. I don't understand its.

So... I have data in Parse JSON version. I want to convert date from JSON on formatdatetime 'yyyy-MM-dd'. All data are string formattig, but flow just convert some dates. Others have error:

Unable to process template language expressions in action 'FormatDataTime' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '27-03-2025' was not valid. The datetime string must match ISO 8601 format.'.

Screens:

I try different version: string(), formatdatetime(), parsedate time. Error always is the same.

I don't any idea, where are problems.

Categories:

Building flows

I have the same question (1)

All responses (19)

Answers (1)

Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
'02-04-2024' is not a date in Power Automate.

First use parseDateTime() to make it a date.

parseDateTime('02-04-2024', 'es-es', 'dd-MM-yyyy')

This returns the ISO-8601 formatted date string 2024-04-02T00:00:00.0000000

Was this reply helpful? Yes No
Sbsyx 113 on at

Like (0)

Report
Copy link

Link copied!

unfortunately, the problem still persists. For some records it converts without a problem, for others the above-mentioned error appears. All data from one JSON.

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

Like (0)

Report
Copy link

Link copied!

Can you show the JSON?

Was this reply helpful? Yes No
Sbsyx 113 on at

Like (0)

Report
Copy link

Link copied!
Yes, of course.
{ "type": "array", "items": { "type": "object", "properties": { "� Country": { "type": [ "string", "null" ] }, "Order No": { "type": [ "string", "null" ] }, "Invoice No": { "type": [ "string", "null" ] }, "Reseller Name": { "type": [ "string", "null" ] }, "Reseller ID": { "type": [ "string", "null" ] }, "Reseller PO": { "type": [ "string", "null" ] }, "Subscription Start Date": { "type": [ "string", "null" ] }, "Subscription End Date": { "type": [ "string", "null" ] }, "Microsoft Subscription ID": { "type": [ "string", "null" ] }, "Manufacturer Part Number": { "type": [ "string", "null" ] }, "Product Description": { "type": [ "string", "null" ] }, "TD Article Number": { "type": [ "string", "null" ] }, "Charge Type": { "type": [ "string", "null" ] }, "ChargeStartDate": { "type": [ "string", "null" ] }, "ChargeEndDate": { "type": [ "string", "null" ] }, "Sales Price": { "type": [ "string", "null" ] }, "Quantity": { "type": [ "string", "null" ] }, "Total Sales Price": { "type": [ "string", "null" ] }, "Currency": { "type": [ "string", "null" ] }, "StreamOne Sales Order": { "type": [ "string", "null" ] }, "End Customer Name": { "type": [ "string", "null" ] }, "CustomerId": { "type": [ "string", "null" ] }, "Tenant": { "type": [ "string", "null" ] }, "SubscriptionCycle": { "type": [ "string", "null" ] }, "Billing Frequency": { "type": [ "string", "null" ] }, "Orderid": { "type": [ "string", "null" ] }, "": { "type": [ "string", "null" ] } }, "required": [ "� Country", "Order No", "Invoice No", "Reseller Name", "Reseller ID", "Reseller PO", "Subscription Start Date", "Subscription End Date", "Microsoft Subscription ID", "Manufacturer Part Number", "Product Description", "TD Article Number", "Charge Type", "ChargeStartDate", "ChargeEndDate", "Sales Price", "Quantity", "Total Sales Price", "Currency", "StreamOne Sales Order", "End Customer Name", "CustomerId", "Tenant", "SubscriptionCycle", "Billing Frequency", "Orderid", "" ] } }

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

Like (0)

Report
Copy link

Link copied!

That's the scheme. I'm more interested in the content (the date formats that occur)

However, a common mistake is to use a date function with null as a parameter.

Was this reply helpful? Yes No
Sbsyx 113 on at

Like (0)

Report
Copy link

Link copied!

I deleted sensitive data:

{
" Country": " PL",
"Subscription Start Date": "28-03-2024",
"Subscription End Date": "27-03-2025",
},
{
" Country": " PL",
"Subscription Start Date": "03-03-2024",
"Subscription End Date": "02-04-2024",
},

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

Like (0)

Report
Copy link

Link copied!

This data looks good and should work with the above expression

Check for differently formatted values or missing values.

Was this reply helpful? Yes No
Sbsyx 113 on at

Like (0)

Report
Copy link

Link copied!

Maybe... Problem is CSV file. I get data from csv and take file apart to create table with data.

Was this reply helpful? Yes No
Sbsyx 113 on at

Like (0)

Report
Copy link

Link copied!

The problem is with the date encoding in CSV, I made a workaround and add the CSV file first to Excel and then to SharePoint.
Interestingly, the problem is the same - some dates are incorrectly encoded:
Unable to process template language expressions in action 'Create_item_2' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '31-08-2023' was not valid. The datetime string must match ISO 8601 format.'.
I used string(), formatdatetime() and nothing will convert these dates - I have the date column in the sharepoint list set as Date/Time format - this is necessary.
I have no idea how to force powerautomate to treat each record as a date.
When breaking down the csv into prime factors, I used base64tostring.
I have no idea how to get around this anymore...

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

Like (0)

Report
Copy link

Link copied!

Use function parseDateTime() to conver the date to ISO-8601

Was this reply helpful? Yes No