Write to Collection Action

[Sekhar___]

​​Hi All ,

I have a cell in workbook which has value 02/04/2021 when im reading the data through get collection stage my collection is filling with 02/04/2021 but when Im re writing back to excel through write collection stage it is writing as 04/02/2021 . To check again I filled the cell with value 15/04/2021 now get collection read as 15/04/2021 to collection and when I am re writing back to excel through write collection stage as 15/04/2021 which is correct. Now my issue why it is writing different format when I am writing 02/04/2021 to excel .


Please help me to understand the issue ..

Thanks

------------------------------
manda janaki sekhar
Sr.Analyst
Deutsche Bank Group
Indian/Mauritius
------------------------------

[michaeloneil]

Hi Manda

This is a common issue with excel and its writing the date as an american date value. When you are writing the value to the cell are you using the excel set cell value action or the write collection action?

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[Sekhar___]

​Hi ,



I hope it shouldn't  write american date value. because it is writing exactly when I write 15/04/2021 why American date value is not working here ?;)

when I am writing back I am using write collection stage ..


------------------------------
Regards
Manda Janaki Sekhar

------------------------------

[michaeloneil · Accepted solution]

This is usually a conversion issue between bp and excel which is why it ends up in american date value, you can see similar issues with dates in database etc. If you are using write collection then after it has been written you could try using the Format cell action in the excel object. Select the whole column to set it as "dd/MM/yyy".

Hope this helps 🙂

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------