Paste Collection (Fast) is making a mess of date formats

[JohnRoberts1]

So I have a process which opens one Excel file and copies the data into a collection. One of the fields is a date, which appears to be imported in a DateTime format (DD/MM/YYY HH:MM:SS) but is stored as Text. 

This process runs for many different reports, and the headers change from one report to another, so hard-coding this to store as Date by default is not possible.

When I use Paste Collection (Fast) to put this data into another spreadsheet, something very strange happens:

Basically, every date from the 1st to the 12th of the month is treated as though it were in the MM/DD/YY format and the date and month are switched, whereas any date from the 13th onwards is correctly formatted as DD/MM/YY.

This is causing an issue when it comes to sorting by date, as all the 3rd of Januarys are being treated as though they were the 1st of March, and so on.

I should point out that this process runs for dozens of reports with thousands of lines, so any solution involving manual adjustments of the format in Excel is a non-starter.

It seems this is an issue with the way the Paste Collection process functions. It assumes dates are in MM/DD/YY unless the month is over 12, in which case it must be DD/MM/YY, resulting in this inconsistent output. I can't stress enough how much nobody wants this. The dates should either all be converted to MM/DD/YYYY (which would be annoying tbh), or they should be left alone. The output from this process is utterly unusable as the dates can not be relied upon. 

------------------------------
John Roberts
------------------------------

[Stephen.Jones]

What about if you had a separate collection with defined data types , you could then transfer your original collection into the defined collection so that the column is defined as date/time.

You could then try to write that collection into the required spreadsheet.  As the column is defined as date/time , it might keep the format correct .

------------------------------
Stephen Eric Jones
Blue Prism Developer
Swinton Insurance
Europe/London
------------------------------

[JohnRoberts1]

The problem is this process extracts a few dozen reports, and they all have different headings. Some don't have dates at all. It's not really workable to have a collection with defined data types when we have so many different reports.

------------------------------
John Roberts
------------------------------

[GopalBhaire]

It is possible that the issue is related to the cultural settings of the Excel. The VBO, when opening the Excel document, utilizes the cultural format of the Excel rather than utilizing the local format. This discrepancy can potentially lead to issues if the Excel documents in question originate from regions with different cultural settings. You could test this by modifying the code to use just the local variant of excel.

Edit:

Also, it is worth trying to set the 'Office Authoring language' to something like English India as we use dd/MM/yyyy format.

Thanks
------------------------------
Gopal Bhaire
------------------------------

[shashank.kumar280]

One other thing that I can think of for this is that if you are just moving the data from one spreadsheet to another without modifying it, you could look at using the action "Copy and Paste Worksheet Range" instead of Write Collection. 

I don't know if that would make any difference but something worth trying other than the points mentioned by @GopalBhaire​

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

[michaeloneil]

Hi @JohnRoberts1

Am i correct in assuming that you only need the date and not date time for this? Once its written into the excel file can you use the VBO Excel action Format ​Cell and set the range of cells to the correct date format? 



------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------