Unanswered

Datetime string must match ISO 8601 format. Can't figure it out.

(0) Share

Report

Posted on by Community Power Pla...

Unable to process template language expressions for action 'Condition' at line '1' and column '17747': 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

Here's the condition that has the issue.

formatDateTime(items('Apply_to_each')?['Period of Performance End'], 'yyyy-MM-dd') is equal to

addDays(utcNow(),30,'yyyy-MM-dd')

formatDateTime(items('Apply_to_each')?['Period of Performance End'], 'yyyy-MM-dd') is greater than

formatDateTime(utcNow(), 'yyyy-MM-dd')

The date was originally MM/dd so I changed it to yyyy-MM-dd, but it still shows as the error.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

v-duann-msft on at

Like (0)

Report

Hi @Anonymous

Thank you for posting.

According to your description, you are encountering date conversion issue.

May I known is the date coming in from the Form as a string? If it is then you may need to break it apart and rearrange it to the correct formatting to use it as a DateTime string. If its a date already then you should be able to use DateTimeFormat.

So if ‘Period of Performance End’ is a date type value, we should change first condition to:
formatDateTime(items('Apply_to_each')?['Period of Performance End'], 'yyyy-MM-dd') is equal to formatDateTime(addDays(utcNow(),30),'yyyy-MM-dd')

And if ‘Period of Performance End’ is a string value, we should first convert it to date type. Please help to check output of 'Period of Performance End' first.
Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Hope the content above may help you.

Best regards,
Anna

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

Like (0)

Report

@v-duann-msft,

I changed the first condition to formatDateTime(addDays(utcNow(),30),'yyyy-MM-dd'), but I still receive the same error message.

The dates coming into the form are in date form. All the dates in the spreadsheet are in mm/dd/yyyy format. Do they all need to be changed to yyyy-mm-dd or does that not matter?

Was this reply helpful? Yes No
v-duann-msft on at

Like (0)

Report

Hi @Anonymous

Thank you for your update.

The date format in spreadsheet doesn't matter as long as it's a date type. But I would like to double confirm, whether you save form response to a excel file or a SharePoint list? That's important as we must do date conversion if it's a excel file.

Hope to hear from you soon.

Thanks
Anna

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

Like (0)

Report

@v-duann-msft ,

How do I check where the form responses are saved? Wasn't able to find it on searches.

All the data comes from an excel spreadsheet. Nothing with this flow is being taken from a SharePoint list.

Thanks.

Was this reply helpful? Yes No
Verified answer

v-duann-msft on at

Like (1)

Report

Hi @Anonymous

Thank you very much for your update.

If that's the case, the issue is caused by lack of date conversion from excel.

The date output from excel should be a number like 44317 instead of 2021-05-01 even though they are formatted as date in excel.

What we need to do is using below expression to switch to normal date.
formatDateTime(addDays('1899-12-31',int(items('Apply_to_each')?['Period of Performance End'])),'yyyy-MM-dd')

So your condition could be modified as :
formatDateTime(addDays('1899-12-31',int(items('Apply_to_each')?['Period of Performance End'])),'yyyy-MM-dd') is equal to formatDateTime(addDays(utcNow(),30),'yyyy-MM-dd')

If you want to check date output from excel, you can create another flow for test purpose.
Manually trigger a flow;
List rows present in a table;
Create HTML table and test it.
Then, I can confirm you will see 4xxxx numbers listed here.

After you get it confirmed, you can go back to your original flow and modify expression as I suggested.

Here are multiple similar threads in our forums, you can take a look:
https://powerusers.microsoft.com/t5/General-Power-Automate/Manipulating-Flow-Excel-Date/td-p/365876
https://www.youtube.com/watch?v=E67YoByn9eA

Hope it helps.

Please click Accept as Solution if my post is helpful to you. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Thanks
Anna

Was this reply helpful? Yes No
PoojaSureshbabu 11 on at

Like (0)

Report

Thank you so much i have been working on this error for past two weeks now it just resolved in secs! 🙂

Was this reply helpful? Yes No