Issuse with date format when posting data to Excel via HTTP

[LPerryman]

Hello All, 

I have having some trouble with the format of dates when posted to Excel from a HTTP request. 

My flow takes a CSV file parses it, adds some additioanal columns and sends it chunks to an excel document stored on SharePoint. The CSV contains 9 columns of dates (some are blank) formatted as dd/mm/yyyy. Initially once the flow runs the dates were appearing as mm/dd/yyyy, but month/day were only swapping where the day was >12. 

I have checked the following

- The flow is outputting the dates in the coreect format dd/mm/yyyy.

- Both powerautomate and excels regional setting are the same (UK English)

- Changing the format of the cells in the excel doucument to the non * dd/mm/yyyy option give the dates in the correct format but the dates with days >12 are formatted as text untill you double click in the cell and hit enter, at which point the format updates and the date is displayed correctly. 

- Chaging the format of the columns to text give the dates in the correct format but it is then diffciult to use sort and filter. 

I did consider formatting all the dates in the CSV but i have about 2000 rows of data and 9 columns of dates, which would take hours with an apply to each loop.

The excel document is intended to be a dashboard so beaing able to filter and sort by date would be really useful. Any suggestions on how to resolve this would be really appericated. 

Thank You

Luke

[Chriddle]

> The CSV contains 9 columns of dates (some are blank) formatted as dd/mm/yyyy

Have you used function parseDateTime('value_from_CSV', 'es-es', 'dd/MM/yyyy') to get a valid ISO-8601 date string?

[LPerryman]

i Chriddle, 

Thanks for the response, i have tried converting the date to the serial number format that excel uses. So could do the same with parseDateTime, but am getting stuck by the length of time it takes to complete this. I have included a sample of the array im working with below of what it looks like after being parsed. I can split it and and then use an apply to each, but there are currenty 2000 items in the array so will take ages to run.

Im still learning power automate so there maybe somthing ive missed. 

[
  "Value1||Value2||Value3||Value4||Value5||Value6||Value7||Value8||Value9||Value10||Value11||Value12||Value13||Value14||Value15||Value16||Value17||18/05/2022||05/04/2018||16/05/2018||05/04/2018||05/04/2018||31/01/2020||23/09/2021||20/11/2023||20/11/2023||Value19||Value20||Value21||Value22",
  "Value1||Value2||Value3||Value4||Value5||Value6||Value7||Value8||Value9||Value10||Value11||Value12||Value13||Value14||Value15||Value16||Value17||18/05/2022||05/04/2018||16/05/2018||05/04/2018||05/04/2018||31/01/2020||23/09/2021||20/11/2023||20/11/2023||Value19||Value20||Value21||Value22",
  "Value1||Value2||Value3||Value4||Value5||Value6||Value7||Value8||Value9||Value10||Value11||Value12||Value13||Value14||Value15||Value16||Value17||18/05/2022||05/04/2018||16/05/2018||05/04/2018||05/04/2018||31/01/2020||23/09/2021||20/11/2023||20/11/2023||Value19||Value20||Value21||Value22",
  "Value1||Value2||Value3||Value4||Value5||Value6||Value7||Value8||Value9||Value10||Value11||Value12||Value13||Value14||Value15||Value16||Value17||18/05/2022||05/04/2018||16/05/2018||05/04/2018||05/04/2018||31/01/2020||23/09/2021||20/11/2023||20/11/2023||Value19||Value20||Value21||Value22"
]

 Kind Regards

Luke

[Chriddle]

>  i have tried converting the date to the serial number format that excel uses

I recommend only using date strings in ISO-8601 format within Power Automate. This will prevent many headaches.

> I have included a sample of the array im working with below

This doesn't look like a healthy data structure to me. Is this a copy/paste error? 😉

If you need to insert/update a lot of rows, consider a “bulk update” pattern, e.g. as described here:

https://sharepains.com/2020/06/08/super-fast-update-excel-using-power-automate/#

[LPerryman]

Not a typo 🙂 

The flow is based on a solution by takolota CSV To New Excel Table - Power Platform Community (microsoft.com) there is alot of free text containing inline commas in the origional data.

The flow is currently quite quick it parses the csv and adds it to a blank excel document via a HTTP POST in a few minunites. I must be missing somthing as im not understanding how the bulk update can update the format of the dates?