Support Portal

for ProcessRobot and WinAutomation

Start a new topic
Answered

Datatype on dates in Process Studio and Excel

In the attached process, I insert a date of the format dd-MM-yyyy into a cell in Excel.

When I insert the variable %ddMMyyyy% of the format dd-MM-yyyy into a cell in Excel, then the date converts to the format MM-dd-yyyy.


Can anyone help med with a solution, since it is confusing with the date format MM-dd-yyyy?

prp

Best Answer

Hello Peter, 


After investigation, it seems that there is an issue with Excel 2013, however for newer versions of Excel there is no such issue.


As a workaround, make sure that the type of the Excel cell is Text and in the WinAutomation Process, the DateTime is converted to Text as well. 


Correction, There is a problem with the for format of the dates when dd <= 12.

Can anyone confirm that the dateformat changes when it is inserted til Excel?

Do you have the time to look at this issue?

ADMIN

Hello Peter,


Which version of Excel is installed? 


Also, have you tried to change the format of the cell to Text or General instead of Date?

ADMIN
Answer

Hello Peter, 


After investigation, it seems that there is an issue with Excel 2013, however for newer versions of Excel there is no such issue.


As a workaround, make sure that the type of the Excel cell is Text and in the WinAutomation Process, the DateTime is converted to Text as well. 

I got Excel 2013. And yes I have tried for change the format of the cells in Excel.


Now it looks like there is only a problem with the format of the dates if the day is less than 10.

That means that 17-03-2020 will be inserted correctly. the date 05-03-2020 will be inserted as 03-05-2020.


Please look at the process attached.


prp

Thank you. It is working

Hi,


This is not a Excel version based issue. We are using Excel 2016 but the same issue occurs. As of now, the text conversion workaround is good but our customer also wants the dates to be in a date format for further action, hence all dates which are of type text will need to be updated in a new column using a formula, which is a painful way to achieve this. 

What we verified is that if we copy and paste SQL rows from the data table to Excel, there is no issue. But if we use Write2Excel, that is when the problem occurs for dates with the day <= 12. 

A solution is needed for such cases since this happens only with Write2Excel. Thanks.

I am using Excel 2010, and same happens to me - on two separate bots it has happened now.., so frustrating as if you use a 'Display Message' action, the message box displays the date in the correct format dd-mm-yy.  The workaround does work, but it's not ideal, i've not had to order data by date as yet, but imagine there hopefully is a way to sort a data table in WinAutomation - before you extract it back into Excel? Unsure if you can sort within the document once the dates have been formatted as Text, poor do really though! So hopefully on the developers radar to fix soon!

Login or Signup to post a comment