Read Range convert excel date format automatically

Hi,

To know the full story of the problem please read the “Side Info about this process” at below.
2 days ago we notice when the robot read range from excel, it convert the column with date format “dd/MM/yyyy” to “MM/dd/yyyy”. In some cases, the excel column’s format is also one of the contributions of this cause.

Scenario 1:
The column is being filled up in the correct format “dd.MM.yyyy”. The read range gives a translated column into “MM/dd/yyyy”. I had written a invoke code to overcome this, but this code couldn’t solve scenario 2.

The code will check first cell of the column whether the date is in “MM/dd/yyyy hh:mm:ss” then it will convert the whole column in to “dd/MM/yyyy”

If Date.TryParseExact(DTSheet1.Rows(0).Item(1).ToString,“MM/dd/yyyy hh:mm:ss”,Nothing,Globalization.DateTimeStyles.None,Nothing).ToString=“True” Then
DTSheet1.Select(“Column1 NOT is Null”).ToList().ForEach(Sub(row) row(“Column1”)=Convert.ToDateTime(row(“Column1”)).ToString(“dd/MM/yyyy”))
End if

Scenario 2:
The column is being filled up in the correct format “dd/MM/yyyy”. However, the read range gives a different result mixture of “dd/MM/yyyy” and “MM/dd/yyyy”.

I need help on code like below by using invoke code, and not to remove blank rows in between, because it has meaning when we upload into SAP.

For each row in DT,
If row(“column1”) is Null then
Goto Nextrow
Elseif row(“column1”) is “MM/dd/yyyy” then
change to “dd/MM/yyyy”
Elseif row(“column1”) is “dd/MM/yyyy” then
Goto Nextrow
End if
Nextrow:
Next row

Here is the file for your testing.
working_template.xlsx (204.8 KB)

Side Info about this process:
I have a process where users filled up an excel template and submit it through email then the robot will read the attachment, format it to the correct layout and upload it into SAP. This robot process has run for about a year.

  • No changes in Virtual Machine Date and Time and the UiPath Studio (last Oct is the latest update of the UiPath Studio)
  • Latest update of this process was 10 days ago, but things get weird on 4 days ago.
  • Orchestrator version wasn’t upgraded since last year

Thank you in Advance

Hi @JuneQuan

Use this code in invoke Code and it will work for both the scenarios

inputDT.AsEnumerable.Where(Function (x) Not String.IsNullOrEmpty(x(0).ToString)).ToList().ForEach(
Sub (row)
	row(1) = If(row(1).GetType.ToString.Equals("System.DateTime"), CDate(row(1).ToString).ToString("dd/MM/yyyy"), row(1).ToString)
End Sub)

Instead of row(1) you can provide the column name also like row(“columnName_1”). This is only for the first date column only.

For your reference

Date Issue.xaml (5.3 KB)

2 Likes

Thank you so much, it works well.
But do you have any idea why when the UiPath activity read range it will change the date format of the column? And it just happens all of the sudden.

If the date is in text format it will read it in text format only (i.e., String) but if the date is in date time format it will be read in DateTime Format. Look closely at your input excel file. The date formats are different in input excel. UiPath is reading them correctly.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.