(BP 6.8) How to Solve Date Flips Issue when CnP: MM(month) flips with DD(day) for dates that are undistinguishable between the two

[KamiliaLH]

The process I'm working on exports a CSV file in which the bot will copy the whole worksheet and paste the table from the CSV file to a new blank xlsx file. The csv file contains columns that has dates in mm/dd/yyyy format. When copying and pasting the table, these date values reverse from dd/MM/yyyy to MM/dd/yyyy. However, when checked, both CSV and xlsx date values are still identified with the same numbering format, dd/MM/yyyy, although the dd and MM have flipped. 

I noticed that this is only applicable when the bot cannot clearly differentiate the dd and MM. For example, 20/12/2020 will be clearly as dd/MM/yyyy format as there is no 20 for month. We had tried changing the Windows language and number formats to English (UK) and English (Malaysia/local) because there was a discussion suggesting that the blue prism MS Excel VBO is using English (US) with date format MM/dd/yyyy but it still didn't work.

Please let me know if anyone has a solution to this. Preferably I would want to avoid going into code stage level.

[Walter.Koller]

I am not sure if I understood what happens with the date when it was copied to Excel.
Is the resulting column / cell in Excel formatted as date or text or is it mixed?
Which action do you use to paste the CSV into Excel?

Blue Prism ignores Windows regional settings (format patterns for eg. date and numbers) when Blue Prism language does not fit Windows language.
You could try to set Windows and Blue Prism both to English (US), if not done yet?

You can also try to alter the Global Code in the Excel VBO (search for 'culture' in the code).

If you transfer data into an existing Excel and don't have to always create a new one, you can try to format the columns according to the date format, save the XLS and copy the data into the pre-formatted columns. You could have an empty version of this XLS somewhere, open it during the start of the process execution and save it with new names, so the empty formatted version stays unchanged.