Excel Date Format Is Not Right

[jgregor4]

I have a very strange issue with Blue Prism adding dates into an Excel file.

The language of my machine is all UK, anything referencing US has been removed.

In Excel, if I try to write a US date that isnt a UK date it throws an error

In Blue Prism, it holds the date correctly as UK and wont allow a US date in the date item

When Blue Prism then writes the date into Excel it puts it in US format

I cannot understand why this is happening as all the validation is there in BP and Excel that US dates are not possible yet BP or Excel seemingly converts the date and I have no idea why this is happening

[jgregor4]

And to add to this, if I put a US date from BP into Excel it puts it as UK format - this is only if the data item is text as a Date data item wont accept a US date

[Chakkravarthi_PR · Accepted solution]

Have you tried Format Column:

Other approaches you can try if the Format Column failed: 

1. Explicitly Format the Date : FormatDate([YourDate], "dd/MM/yyyy")
2. Force the Excel to Treat Input as Text with a leading apostrophe:

            '01/06/2025 — Excel will treat this as a literal string, not a date

             Expression:  "'" & FormatDate([YourDate], "dd/MM/yyyy")

Store this in a data item and use Write Collection

[Anton__Hosang]

It's down to the write collection action. It naively transforms the content of the datatable (Collection) Show the entire code of the action you are using to write to excel

[jgregor4]

I'm using the BP Excel VBO and the set value action