Format Date in Excel

[Lily.L]

Hi guys,

How can we use Blue Prism to format the date as dd-MM-YYYY?

I have added date to collection as FormatDateTime([Local Start Time], "dd-MM-yyyy")

However, when I check Excel, the date is showing as MM-dd-yyyy. (1)

After that, when I run a new process, I open that collection again, and I add a few more rows with the date as dd-MM-yyyy. (2)

After saving the collection and opening the Excel to check, I noticed that the date in (1) now showing as dd-MM-yyyy. However the date in (2) showing as MM-dd-yyyy.

The date in Excel is showing as dd-MM-yyyy and MM-dd-yyyy. Are there any ways we can use Blue Prism to make the date standard as dd-MM-yyyy?  Thank you!

------------------------------
Mia Mikela
------------------------------

[PvD_SE]

Hi Mia,

In my experience, Excel cannot be trusted to provide a proper date. Depending on the settings of the creator of the Excel, and the settings on your PC, the date can be formatted differently and even appear as a large integer.

If the Excel is delivered by a third party, then I'd request the creator of the file to adhere to whatever format you require. Alternatively, fix the date format in the Excel prior to it being used in the process.

If all of that is not possible, I'd suggest to import the data into a collection, and then let the process fix the date format prior to processing, or let the process fix it while processing the collection. 

------------------------------
Happy coding!
---------------
Paul
Sweden

(By all means, do not mark this as the 'Best answer'! )
------------------------------

[john.hammond]

I'd agree with everything Paul has put here, although sometimes even then, I've had mixed success with pre-establishing the date format in an Excel template that I open/write to. Honestly, the only way I've been able to consistently ensure that Excel doesn't assume US dates is to pre-format the column as Text and then after writing, have a Macro stored within the template that essentially replicates the Text to Columns/Force Date (DD/MM/YYYY) action. Can't remember off the top of my head whether Run Macro is a defined action within the default VBO - if not, just create an action with an input of 'Macro Name' (text) and 'Handle' (number), pass in the relevant Excel Handle and name of the saved Macro and enter this into the code stage: GetInstance(Handle).Run(Macro_Name)

------------------------------
John Hammond
------------------------------

[Anton__Hosang]

Best to just

get the OA Dates of the Dates using DateTime.ToOADate() (e.g. either you have another action in Date & Time, or you code it into the Excel VBO)

i) Format the Excel Range as "General"

ii) Check if the Date System is 1904, and subtract 1462 days from the OA Dates if it is

iii) put the dates in as OA Dates (numbers) in the Excel Range Cells

iv) Re-Format the Excel Range to "dd-mm-yyyy"

Quite fast, and I've never known it to fail!

------------------------------
Anton Hosang
RPA Developer
Smart Automation Services
Europe/London
------------------------------